How to make text color to change in a table when drop down list changes in another cell

[benji23]

I have an Excel spreadsheet which I need help with. Characteristics:

I have a drop down menu in column C.
I have a dates from column F to J (5 days).
I have 15 rows (names) from row 8 to 22.
F to J is to determine staff productive for a day/week.
I need F8 to J22, to change text colour to green when 15 or above is entered. If below 15 I need the text to be orange depending on what is selected in the drop down menu in C.

So If I have item "STU ENT" selected in C, I need text in F8 to J22 to be green when 15 or above is entered. If below I need orange.

If "STU EXP" is selected in C, I need text in F8 to J22 to be green when above 8, orange when below.

I can get it to work with conditional formatting but only when 15 is entered for all drop down menu items, but I need this to be different for each selection in the drop down list...

Help would be so great Spreadsheet performance tracker2.xlsxand save me so much time.

Thanks in advance.

[Glenn Kennedy]

2 CF formulae orange:

IF($C8< >"",F8< IF($C8="STU ENT",15,IF($C8="STU EXP",8)),FALSE)

and green:

IF($C8< >"",F8>=IF($C8="STU ENT",15,IF($C8="STU EXP",8)),FALSE)

I had to delete your mass of CF formulae in the sheet to make life easier for me!!

[benji23]

Hi Glenn,

Thanks so much for the reply, it is exactly what i am after.

I've tried making it so i can get it to work with all of the drop down list and targets (drop down list has now been adjusted as i don't need as many entries)...


DEATH 15

FAM 25

STU ENT 15
STU EXP 8

OACD 15
OACD EXP 9

W/A 15
W/A EXP 12

APPEAL 5

Thanks again!