Conditional Formatting to highlight text plus adjacent cell

[mpitre]

I'm having problems using conditional formatting to highlight two adjacent cells. My goal is to highlight the cell in column B where the term "flight checklist" is and also highlight the cell immediatly adjacent to that cell. My cell range is A1:B265. So in the given example of "Standard Checklist" falling in B3 I would like to highlight B3 and A3. I know that I can use conditional formating to highlight cell that contains text and format it to fill in red (or whatever color). But I don't know how to also highlight the cell immediatly to the left of the highlighted cell. Would I create an additional rule with an =IF statement saying if True highlight adjacent cell?
I thought I could use something like =IF(A1:B265="Standard checklist",....

Thanks for your time and help, it is greatly appreciated.

[FlameRetired]

With lookup term in A1
With 'Applies to' set to =$A$3:$B$265

and Rule type:
"Use formula to determine which cells to format"

this formula

Please Login or Register  to view this content.

Note the relative / absolute cell referencing.

[Richard Buttrey]

Hi,

Not quite sure whether you are only dealing with A3:B3 of the whole of column B.
If the former then use the CF:

Please Login or Register  to view this content.

and apply it to A3:B265,

If the latter then

Please Login or Register  to view this content.

[mpitre]

With lookup term in A1
With 'Applies to' set to =$A$3:$B$265

and Rule type:
"Use formula to determine which cells to format"

this formula

Please Login or Register  to view this content.

Note the relative / absolute cell referencing.

Wow Thanks for the quick response. I've added the new rule but still can't get the cell A3 to also highlight any time the text criteria appears in column B. This list will grow in time and the goal is to have the cell "Standard Checklists" highlight when it appears in Column B in addition to the adjacent cell. I understand the reference needs to be absolute only to B, so $B3 is correct. But $A3 would also need to be highlighted. The heading up top in A1 will not be highlighted. I'm really sorry for any confusion.. Thank you so much for helping me wrestle with this Excel puzzle.

[mpitre]

Hi,

Not quite sure whether you are only dealing with A3:B3 of the whole of column B.
If the former then use the CF:

Please Login or Register  to view this content.

and apply it to A3:B265,

If the latter then

Please Login or Register  to view this content.

Hi Richard, I'm dealing with the whole column B. Would I place your =AND formula in a separate rule?

[FlameRetired]

But $A3 would also need to be highlighted. The heading up top in A1 will not be highlighted. I'm really sorry for any confusion.. Thank you so much for helping me wrestle with this Excel puzzle.

It's working at my end. Please see attached.

The reference to $A$1 is where you would place your lookup value. It is not included in the CF 'Applies to:' range. That is =$A$3:$B$265 and not =$A$1:$B$265

In this case A1 is currently 'Standard Checklist'. Find a drop down in A1 where you can change that and see what happens. It highlights the whole rows in the attached.

[mpitre]

It's working at my end. Please see attached.

The reference to $A$1 is where you would place your lookup value. It is not included in the CF 'Applies to:' range. That is =$A$3:$B$265 and not =$A$1:$B$265

In this case A1 is currently 'Standard Checklist'. Find a drop down in A1 where you can change that and see what happens. It highlights the whole rows in the attached.

It worked, it worked, it worked. I'll go over how you did it so I can add to my Excel tool box of knowledge. Was I far off? I create the two separate rules or just add one to the "contains Text" rule? Thank You Thank You Thank You. I'll mark as solved.

[FlameRetired]

You are welcome.

No that should be the only rule you need. No need for "contains Text" rule.

Thank you for the feedback, added rep and marking this thread Solved.