Conditional Formatting based on range of cells

[MISSYDANNI]

[TABLE="width: 500"]
<tbody>[TR]
[TD]

[/TD]
[TD]RED
[/TD]
[TD]AMBER
[/TD]
[TD]GREEN
[/TD]
[/TR]
[TR]
[TD]DAILY
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WEEKLY
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MONTHLY
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

After some conditional formatting advice please...

I have 2 sheets in an excel workbook, the content of sheet 1 is a pull through from a table in sheet 2.

I would like to be able to conditionally format the cells on sheet 1 based on which column the user populates in sheet 2.

Can anyone help me with what I am sure is actually quite a simple thing but to my post bank holiday brain is a step too far

Sheet 2 table is as above, sheet 1 just contains the contents of column A, ie. Daily, Weekly, Monthly and these are the cells I need to conditionally format

Thanks in advance,

Danni

 

[Marcelo Branco]

You need 3 rules

Select A2:A4 in Sheet1 and use the Formula option in CF

Rule 1
Use this formula
=MATCH("Yes",INDEX(Sheet2!B:D,MATCH(A2,Sheet2!A:A,0),0),0)=1
Format button, Fill --> red

Rule 2
Use this formula
=MATCH("Yes",INDEX(Sheet2!B:D,MATCH(A2,Sheet2!A:A,0),0),0)=2
Format button, Fill --> amber

Rule 3
Use this formula
=MATCH("Yes",INDEX(Sheet2!B:D,MATCH(A2,Sheet2!A:A,0),0),0)=3
Format button, Fill --> green

M.