SOLVED

Conditional Formatting to highlight whole row

Copper Contributor

We have a big product database and I'd like to be able to get excel to highlight the whole row if a product contains the text "EOL".

 

It seemed quite straightforward but I can't get it to work.

 

The column with the product name could be Stone EOL or Porcelain EOL for example so it wouldn't just be the phrase "EOL" it is searching for

 

Any help much appreciated!

4 Replies
best response confirmed by amandaSST (Copper Contributor)
Solution

@amandaSST 

Let's say that the product name is in column B and that the data are in rows 2 to 1000.

  • Select rows 2 to 1000. The active cell in the selection should be in row 2.
  • On the Home tab of the ribbon, click Conditional Formatting > New Rule...
  • Select 'Use a formula to determine which cells to format'.
  • Enter the formula
=ISNUMBER(FIND("EOL",$B2))
  • Click Format...
  • Activate the Fill tab.
  • Select a highlight color.
  • Click OK twice.
That's it thank you!!
This worked wonderfully, thank you!
But is there a method to reach this goal, that still allows me to manually fill a cell in that row with a different color?
It seems that the formula overwrites immediately, and the entire row stays highlighted.

Thanks in advance,

- Tony

@DraygonTech 

Conditional Formatting always overrides manual formatting - that's the whole idea...

1 best response

Accepted Solutions
best response confirmed by amandaSST (Copper Contributor)
Solution

@amandaSST 

Let's say that the product name is in column B and that the data are in rows 2 to 1000.

  • Select rows 2 to 1000. The active cell in the selection should be in row 2.
  • On the Home tab of the ribbon, click Conditional Formatting > New Rule...
  • Select 'Use a formula to determine which cells to format'.
  • Enter the formula
=ISNUMBER(FIND("EOL",$B2))
  • Click Format...
  • Activate the Fill tab.
  • Select a highlight color.
  • Click OK twice.

View solution in original post