Referencing another cell and current cell in Google Sheets Conditional Formatting range

Question

I have a column A which contains text values of two options: "One" or "Two". I have a series of columns (B:D) alongside which contain either the letter "A" or "B".

I want to create four conditional formatting rules over the range of columns B:D, and I want them to be coloured as such: a light green for instances of "A" if column A value is "One"; dark green for instances of "A" if column A value is "Two"; light red for instances of "B" if column A value is "One"; dark red for instances of "B" if column A value is "Two".

I have found a lot of info on referencing another cell's value in a conditional formatting formula, but I can't work out how to connect that to a cell where there is more than one value option, and how to expand that across a range of cells... any guidance much appreciated!

Answer 1

You can use the following custom formula in your Conditional Format rules:

Range: B1:D

One - A [light green]: =AND($A1="One",B1="A")

Two - A [dark green]: =AND($A1="One",B1="B")

One - B [light red]: =AND($A1="Two",B1="A")

Two - B [dark red]: =AND($A1="Two",B1="B")

• You will notice, I used $ in referencing A1 which means that only the row number will increment and the column will be locked at Column A. While there is no $ when I reference B1 this means that both column and row index will increment based on the selected range.

Output: