Excel - programm cells to change colour based on another cell

Question

I am trying to create a formula for Excel whereby a cell would change colour based on the text in the previous cell.

So for example if cell B2 contains the letter X and then B3 is Y, I would like B3 to turn green.
Equally, if B2 contains X and B3 contains W I would like B3 to turn red.

Any ideas much appreciated!

Answer 1

1. Select cell B3 and click the Conditional Formatting button in the ribbon and choose "New Rule".
2. Select "Use a formula to determine which cells to format"
3. Enter the formula: =IF(B2="X",IF(B3="Y", TRUE, FALSE),FALSE), and choose to fill green when this is true
4. Create another rule and enter the formula =IF(B2="X",IF(B3="W", TRUE, FALSE),FALSE) and choose to fill red when this is true.

More details - conditional formatting with a formula applies the format when the formula evaluates to TRUE. You can use a compound IF formula to return true or false based on the values of any cells.

Answer 2

Select ColumnB and as two CF formula rules apply:

Green: =AND(B1048576="X",B1="Y")

Red: =AND(B1048576="X",B1="W")