Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Conditional formatting for entire row based on data in one cell

Copper Contributor

I need all cells in a row to highlight a certain color if the data in one cell contains a specific word. 

 

What I specifically want is for an entire row to turn grey if the status cell contains the word "SHIPPED." I know how to make that specific cell highlight the color I want, but not the entire row of the sheet.

 

Thank you for your help!

95 Replies

@Ebony_T_0524 

Let's say you want to format rows from row 2 to row 100 that contain "sales" with possibly other text in column D.

Select rows 2 to 100.

The active cell in the selection should be in row 2, for example A2.

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(SEARCH("sales", $D2))

 

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

Worked perfectly. I only wanted to do certain columns in the row so I just switched the 'Applies to' to the columns I wanted.

Thanks Again
Sorry to pile on with new question, but you all have such great responses! I'm using =LEFT(FILTER(range,criteria,"")10) to list only the first 10 characters from a filtered list of employees. I then want to conditionally format $B1:$J800 when the results of $C1 ="Text". If I use CF when text contains "Text", it will successfully highlight the cells containing "Text", but won't highlight the entire row, even if applies to $B1:$J800. If I use a formula to CF, it's never true because the cell doesn't ="Text", it equals the Filter formula. Any advice?

@Renee Bugbee 

Select B1:J800. The active cell in the selection should be in row 1.

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(SEARCH("Text",$C1))

 

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

You're Awesome Hans!!! Works like a charm.
Thanks :)

Regarding your reply/solution at 1348 hrs on 29 Aug, 2019, here, ::

 

This didn't work. I wanted to highlight b1:e1 when there is some specific text in a1. I've typed this conditional formula: =A1="some_text" and applied it to this: =$B$1:$E$1000. But when there is desired value in a1, only b1 is highlighted. I've tried many tries. Thanks again.

@BathindaHelper 

Change the formula to

 

=$A1="some text"

 

The $ before the column letter A fixates the column, so that B1, C1, D1 and E1 all look at A1.

it also worked with me, thank you

@Sergei Baklan 

 

Hi Sergei, 

 

I am drawing blanks on a current issue I am having with the excel formatting (see snippet below). The table on the left side has over 4500 rows of hazardous material detail. I utilized a XLOOKUP formula for single row of data to automatically fill in the respective row on the large table by UN number. Is there a way to have this row automatically appear at the top so you don't have to scroll down. I highlighted the PSN, but would still have the row get filtered to the top. I hope I got the message across. Thank you! 

 

Cam_Mango205_0-1704210707634.png

 

@AUSTXCHICK 

Take a look at this 5 minute video instead of searching this page for half an hour :

https://youtu.be/XHT4paRaY4g                 Excel Conditional Formatting with Formula | How to Get it RIGHT Every Time

 

 

@Sergei Baklan 

 

I need to be able to highlight A4 if E4:Q4 does not contain "R". I know how to do it for finding a value but not for not finding it. I have tried putting NOT in front of my formula but did not work.

MEdwards26_0-1706136170249.png

 

@MEdwards26 

Try

 

=COUNTIF(E4:Q4,"*R*")=0

 

or

 

=ISERROR(SEARCH("R",E4:Q4))

Thank you very much. Particularly the last sentence of your answer that explained the reason as well. Thanks again. You people are really great.

@AUSTXCHICK   thank you so much.  saved me a lot of aggro!

lifesaver! Thanks a lot!