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!

122 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 @SergeiBaklan . 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.

@SergeiBaklan 

 

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!

@HansVogelaar 

 

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

@SergeiBaklan 

 

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"

@tianathomson 

Your screen shot reveals the problem.  Your formula of =$E$2="completed" has a cell reference that is fixed (nonvarying) both for the column and the row; i.e., only cell E2 will be used, regardless of which cell the formula is applied to.  You want a fixed column (E) but a varying row.  So use a formula of =$E2="completed"

 

@SnowMan55 

 

Hello!

I am struggling with getting multiple CF rules to run in sequence. My goal is to have the row color change as new information is entered into specific cells in specific columns. However, I don't know how to write the formula.

JavaJunky_0-1719340019181.png

 

@JavaJunky 

CF iterates ranges cell by cell, apply to the cell formatting for the first rule which returns TRU and ignores other rules:

image.png

If we change rules order that will be

image.png

Depends on your goals use according order of rules.