Forum Discussion
Solved
Conditional formatting for entire row based on data in one cell
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!
Hello
Assuming that D is the column containing the status cells, and you wish to apply conditional formatting to rows 1:50, here are the steps:
1. Select any cell in row 1
2. Go to 'Conditional Formatting>New Rule>Use a formula to determine which cells to format'
3. In the formula field paste =$D1="Shipped", set the required format and click 'OK'
4. Go to 'Conditional Formatting>Manage Rules' and locate the rule you have just created
5. In the field 'Applies to' paste '=$1:$50', or just select the required rows, starting from row 1, then click 'OK'.
If you type the word 'SHIPPED' in any cell of the column D (rows 1 to 50), the entire row will be conditionally formatted
Hope this helps
Yury
You may create the rule with the formula
=$A1="Shipped"and apply it to your entire range, let say $A$1:$Z$200
All rows with "Shipped" will be in desired color.
A1 in formula shall be top left cell of your range.
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!
For entire column you shall use $K1 in formula, perhaps you use $K2 instead.
- so I am using version 16.66.1 and trying to do the same. So in column A (for say 100 rows) I want to highlight using the following format. If i put a 1 I want the row to be green. 2=yellow 3=white (no fill) or 4 is red. I am struggling to figure that out.
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!
