Conditional Formatting for Multiple Cells

[ScoutLaser]

Hello, 

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 1	I'd like this colored blue	I'd like this colored blue	I'd like this colored blue	XYZ (Colored blue)
Example 2	I'd like this colored yellow	I'd like this colored yellow	I'd like this colored yellow	ABC (Colored yellow)

How can I go about making this happen?

Thanks so much! 

[#2]

@ScoutLaser 

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.

EDIT:

The rules for conditional format are:

=$E2="ABC"

and

=$E2="XYZ"

[#3]

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?

[#4 · Accepted solution]

@ScoutLaser 

=OFFSET(A1,0,3)="ABC"

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

=$A$1:$H$26

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

[#5]

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