Excel conditional formatting depending on cells on another column

Question

I want to apply conditional formatting in the cell's in one Excel column so that they change appearence depending on the text in the cell's in other column (but in the same row).

Cell B1 depends on A1
Cell B2 depends on A2
Cell B3 depends on A3

Conditional formatting rule, example for one cell:

Rule: =A1="X"

Which applies to: =$B$1

How can this be done to all the cells in column B?

Answer 1

Apply the formatting to $A$1:$B$3, and set the rule to =$A1="X".

The general concept here is to freeze the column but allow the row to change. This will cause each row to apply formatting to the entire row if column A is equal to X.

Answer 2

For just your range, applies to:

=$B$1:$B$3

or for the whole of column B, applies to:

=$B:$B

Answer 3

You can use =EXACT("X",$A1) as rule for the conditional formatting of B1. Dragging it down will results in =EXACT("X",$A2) for B2 and so on.

Answer 4

Do it like this. that way the formatting is relative to each cell