Conditional Formatting based on range of cells

[MISSYDANNI]

	RED	AMBER	GREEN
DAILY		Yes
WEEKLY	Yes
MONTHLY			Yes

<tbody>
</tbody>

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.