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!

116 Replies

Here is a graphical example. Any cell in the data column that matches any one of the unique reference cells I want to highlight that row. Assume the Reference column needs to go on a separate tab?

 

2024-03-07 09_30_12-DevServerGXPMatching.xlsx  -  Read-Only - Excel.png

I am ecstatic. This is a game changer for me. Thank you!

@Yury Tokarev 

Thanks this is great, is it possible to make it so it only changes the color of the row to a specific column rather than the entire row?

@Yury Tokarev 

Good morning, 

 

Appreciate the guidance you provided on this thread. for some reason, I inputted the instructions and for some reason when I select "No-show" from the drop down on Column " F" the red goes down a row. So it is not highlighting the row that the drop down in in. Please advise and thank you Yury. 

antmac_0-1711461988284.png

 

 

antmac_1-1711462021466.png

 

@antmac 

Most probably references in formula and in Apply To range are not in sync. For example

image.png

we use in formula reference with first row and apply to the range which starts from the second row. If to change on row 2 in formula, it works

image.png

@Yury Tokarev 

I believe I have followed your instructions correctly, but the desired result only works in the first row.

The desired result is that any row from 5 to 500 that contains a qty other than zero in column F be highlighted.

Thank you in advance for your help.

Hello @Sergei Baklan . I saw your numerous responses in another discussion. I'd be grateful if you can help me.

I wish for a row to become highlighted if one cell on this row in column Y: 1) has a date and 2) the date is today or in the past.

Thank you!

@DavidCaughman 

I believe I went into conditional formatting and selected 'use a formula to determine which cells to format'. Then, I put $H1='N'. The $ keeps the column consistent while the row may change. So, in your case, it would be $F5>0. I am not able to troubleshoot this at the moment, but let me know if it works.

@NatalieHarvey 

Let's say you want to apply this to rows 2 to 100.

Select these rows; the active cell in the selection should be in the top row (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

=AND($Y2<>"", $Y2<=TODAY())

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

@Sergei Baklan 

 

Hey there, 

Your solutions are awesome! Could you help me understand why my last row keeps being excluded from my conditional formatting? Even if I add all rows $J:$J a specified number of rows $1:$3000, it keeps excluding the last row when I adjust the sorting. For reference, I am conditionally formatting columns A through J based on the text in column K.

 

Thanks!

@Hans Vogelaar 

 

Hi - hopefully it is ok if I ask you the same question. I am not sure if the other user is still active. It is below. 

 

Thanks!

 

Hey there, 

Your solutions are awesome! Could you help me understand why my last row keeps being excluded from my conditional formatting? Even if I add all rows $J:$J a specified number of rows $1:$3000, it keeps excluding the last row when I adjust the sorting. For reference, I am conditionally formatting columns A through J based on the text in column K.

@ExperiencedNovice 

For entire column you shall use $K1 in formula, perhaps you use $K2 instead.

WOW - that was it. You are really such a wonderful resource. Thank you!

@Yury Tokarev 

 

PFB solution sheet. Please let me know if it helps

 

I have attached a screenshot also Capture 1.PNG

@Sergei Baklan 

 

I'm trying to do a similar type of formatting where a row is highlighted when the end cell says completed. It works fine for one row however when I add more to the range the entire range is highlighted despite only the first row containing "completed" in the appropriate column. Screenshot 2024-06-21 220053.pngHow do I make it so the row is only highlighted if the cell in column E for that row says "completed"