Highlight cell by certain colour based on 3 criteria

[Shonky]

Gday all,


I have a table that requires some assistance. I have cells (A2:A14) that needs to be colour coded based off the cells K2:K9 but have to meet certain requirements within the data set from E2:G14. For an example, A5 will need highlighted the colour within K6 because it fulfills the requirements for that colour being its a D (E5), Int (F5), and Tall (G5). The different criteria is tabled within the cells M2:M9, I can arrange them as need be, just need the starting formula to make it all work.

Any and all help is always appreciated.

Thanks,

Shonky

[AliGW]

You will need to set up a rule for each colour. To help us help you, please manually shade the table so that we can see what we are aiming for.

[JohnTopley]

For A5

Select A2:G14

CF formula

=AND($F2="Int",$G2="Tall",$E2<>"G")

Fill: Purple

[JohnTopley]

For "Pink"

=AND($E2="G",$F2="Int",$G2="Tall")

For "Red"

=AND($E2="G","$F2=Int",$G2="Standard")

etc

[Shonky]

Will I have to write a rule out for every letter ie =AND($E2="A",$F2="Int",$G2="Tall")
=AND($E2="B",$F2="Int",$G2="Tall")
Or can I give it a series to check over? ie from A-F then H-M?

[AliGW]

You could try this:

=AND(OR($E2="A",$E2="B",$E2="C",$E2="D",$E2="E",$E2="F"),$F2="Int",$G2="Tall")

[JohnTopley]

see this ...

=AND($F2="Int",$G2="Tall",$E2<>"G")

i,e. therefore must be A=F, H-M

which is why I used this example

Your initial post excluded L & M examples so may need to Exclude as above

[Shonky]

Thank you so very much for your assistance on this one. I'll be able to make good use of these formulas for years to come.

[AliGW]

Thanks for the rep.

[JohnTopley]

And thanks from me for the rep!