Forum Discussion
Conditional Formatting for Multiple Cells
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!
=OFFSET(A1,0,3)="ABC"You can try this and 3 additional rules for conditional formatting in the attached file.
=$A$1:$H$26This is the range the format applies to in the example.
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.
EDIT:
The rules for conditional format are:
=$E2="ABC"and
=$E2="XYZ"- 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?=OFFSET(A1,0,3)="ABC"You can try this and 3 additional rules for conditional formatting in the attached file.
=$A$1:$H$26This is the range the format applies to in the example.
