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 th...
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.
- WOW - that was it. You are really such a wonderful resource. Thank you!
- 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!
SergeiBaklan I'm seeking to sum dollar values in one column where the conditional formatting rule of "cell that contains "insurance"" in another column. Was first using the conditional formatting of highlighting cells that contain "insurance" then trying to sum another column in that same row. my hangup is that the cell is not an equal to rather 'contains' a word. Suggestion?
Sorry, I didn't catch why do you need conditional formatting to sum some cells with condition. You may use something like
=IF( <some cell> = "insurance", SUM(myRange), "")
or like
Privet Sergei!
I can't get it to work for the entire row. Tried multiple times. When I apply it to the signed cell, there is no problem. When I specify a range, it doesn't work. Please help! 🙂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.
Sir, please help me with conditional formatting. In My Excel data, I need to highlight cells of column F only when I enter text 'ok' in the cell of column 'p' in the same row. when i apply =$p$3="OK" it highlights cell F3, but I can't apply this condition for the entire row.
Select the entire range that you want to format. The active cell should be in row 3.
Create a conditional formatting rule of type 'Use a formula to determine which cells to format', with formula
=$P3="OK"
No $ before the row number!
- HansVogelaar,
Thank You Very Much to help me, it worked perfectly.
Thank you for your response SergeiBaklan
Sorry I'm a total novice, but when I followed your instructions (I think), it only highlighted the cell that contained the word "shipped" instead of the entire row. How can I make each individual row turn grey when the cell in column G for that row contains the word "shipped?"
Thank you so much!
