Conditional formatting using different colours

[Cecilia Kovacs]

Hi, 

I have a table containing children who are enrolling for instrument tuition. I have a column set to Y (Yes) is a child returned the slip from the letter, and another set to Y, when they have made the online payment. I want to have a rule to highlight those who already paid in green, and those who just returned the slip yellow, leaving the rest of the table with red cells (those would be children who we had from last year and likely to continue but not taken any action yet).

The list keeps growing and my colleague who might input data doesn't necessarily know how to follow my rule, so I want to make it automatic.

How do I do this? (Sorry I am totally new to conditional formatting.) I am attaching the table where I manually formatted the cells and bunched them into these colours. Names are taken out obviously for data protection purposes. 

Many thanks for any help out there.

[#2 · Accepted solution]

Hi Cecilia,

First, better to apply your rule not to entire column but with for some range with gap within which you data will be. Another option dynamic range or table. Otherwise it works quite slow or even hangs.

When apply 3 rules for yellow, green and red accordingly

=($F2:$F500="Y")*($G2:G500="Y")
=($F2:$F500="Y")*ISBLANK($G2:G500)
=ISBLANK($F2:$F500)*ISBLANK($G2:G500)*NOT(ISBLANK($B2:$B500))

again to the range with gap

=$A$2:$G$500

Please see file attached

[#3]

Thank you so much Sergei! You have helped enormously. Much appreciated.