3

I have a list of pages in column A. I want certain cells in column B to be highlighted if the cell B1 contains the word Hotel. See below example where cells B2, B5, B8, and B10 have a background color red because B1 has the text Hotel.

I tried using the built-in conditional but that works if the cells contain a keyword.

Excel example

AndrewH
  • 49
  • 12
  • You indeed need conditional formatting, but you can use a formula instead of checking against the cell value, even though that probably is still enough. – LPChip Nov 05 '19 at 20:09

2 Answers2

3

You indeed need conditional formatting. But instead of looking for a specific cell, you need to set a formula instead.

Make your formula: =$B$1=Hotel

And set your colors accordingly.

The easiest approach here is to set this up for cell B2, then copy B2 to all other cells that you need this formatting for. Alternatively, select all cells first, then setup the conditional formatting as specified above.

The use of the $ in the cell notation makes this a fixed cell, such that copy/paste the cell around will not alter the lookup.

LPChip
  • 59,229
  • 10
  • 98
  • 140
2

1. Create a conditional formatting rule with the following formula:

enter image description here

2. Target cells for custom formatting using multiple absolute references:

enter image description here

This will do what you want:

enter image description here

Mr Ethernet
  • 4,191
  • 2
  • 16
  • 28