Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

vba

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

like image 931
user2878987 Avatar asked Oct 14 '13 13:10

user2878987


Video Answer


1 Answers

  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.

like image 133
Joe Laviano Avatar answered Oct 14 '22 15:10

Joe Laviano