Conditional formatting - result depends on value of two cells

Hi,

I've tried - but I can't work out how to achieve this

If (D5) = "6" and (BK5) = either "A" or "B" or "C" then colour (A5) purple

I have tried
=AND($D$5="6",$BK$5="A")
=AND($D$5="6",$BK$5="B")
=AND($D$5="6",$BK$5="C")

as three sep rules in (A5), but no joy...

Any words of wisdom please?

( I have other rules running on (A5) too - all of which work fine)

Many thanks

Highlight A5 > Conditional Formatting > New Rule > Use a formula

=AND(D5=6,OR(BK5="A",BK5="B",BK5="C"))

Format however you'd like.

Note that "6" and 6 are different to Excel.

Try

=AND($D5=6,OR($BK5="A",$BK5="B",$BK5="C"))

is the 6 TEXT or number; if the latter, remove your quotes as per above

Thank you very much - both work great. why the $ in one and not the other please?

The dollar sign ($) before the column letter will keep the column reference static if the Conditional Formatting is being applied to multiple cells.

I did not include it in mine because you did not mention any cells other than A6 to receive CF.

Thanks for the rep! If that solved your question, please mark this thread as SOLVED.