Not sure what you want to change the colours of, but i have made

Drama in d339 change f341 to e351 Purple

MFL in F339 change f341 to g351 orange
highlight the top left cell of the range (d341) then down to the end cell (e351) so you have all the cells selected of the range you want to change the colour of, then click conditional formatting > New Rule > Use a formula > then in the box type in the formula that can be true or false - =$D$339="Drama" click format and choose the colour you want. Click ok

Then in the Conditional Formatting Rules manager, you should be able to duplicate the rule and amend the Subject and colours.


I think using countifs complicates it a bit. Aldso noticed that some of the headings have a space at the end, which caused some issues......

Attached Files

Copy of September return Staff V4_Purfleet.xlsx (62.1 KB, 8 views)

Hi Purfleet

I opened the spreadsheet and all the conditional formatting is not there.

Tried what you stated and it does not work

the $ prevents its from copying?

Thank you

Attached Files

September return Staff V4.xlsx (88.0 KB, 6 views)