Conditional Formatting for Multiple Cells

New Contributor



I am trying to create an excel spreadsheet that will accept conditional formatting but have it apply to multiple cells. 


Using the example below, when I enter "XYZ" in the 5th column I would like it to assign a color to the preceding 3 cells as well. The same would then be true for "ABC" but a different color. 


Example 1I'd like this colored blueI'd like this colored blueI'd like this colored blueXYZ (Colored blue)
Example 2I'd like this colored yellowI'd like this colored yellowI'd like this colored yellowABC (Colored yellow)


How can I go about making this happen?


Thanks so much! 

4 Replies


In the attached file the conditional format applies to range =$B$2:$E$21

This means if you enter "XYZ" or "ABC" in any cell in range =$E$2:$E$21 the 3 cells to the left are highlighted as well.



The rules for conditional format are:





Hmmm this is close but ever so slightly too specific for my use case.

The sheet I am using actually has multiple columns and so the goal would be to be able to insert "ABC" anywhere in the sheet and also have the preceding 3 cells change color there. Apologies for not specifying that earlier.

Is that still possible?
best response confirmed by Hans Vogelaar (MVP)



You can try this and 3 additional rules for conditional formatting in the attached file.


This is the range the format applies to in the example.

conditional format.JPG

That's the one! Now just to enter a heck of a lot of formatting rules for different letters haha. Thank you!