Use Conditional formatting to turn a cell Red, yellow or green depending on 3 values in another sheet

8
votes

I have a table with 262 rows and 52 columns of data(numbers). I want to set each one of the cells to a RAG(Red,Amber,Green) format(Conditional maybe). The cell needs to reference another sheet(new sheet) with 3 columns(A,B,C) and 262 rows. The formula for the 3 RAG values needs to look like:-

if the value A100 <= column A,B,C100(new sheet) then turn cell Green if the value A100 = or 10% greater than column A,B,C100(new sheet) then turn cell Amber if the value A100 = or 20% greater than column A,B,C100(new sheet) then turn cell Red

1
excelvba

1 Answers

15
votes
  1. Highlight the range in question.
  2. On the Home tab, in the Styles Group, Click "Conditional Formatting".
  3. Click "Highlight cell rules"

For the first rule,

Click "greater than", then in the value option box, click on the cell criteria you want it to be less than, than use the format drop-down to select your color.

For the second,

Click "less than", then in the value option box, type "=.9*" and then click the cell criteria, then use the formatting just like step 1.

For the third,

Same as the second, except your formula is =".8*" rather than .9.