Forum Discussion
Conditional formatting for entire row based on data in one cell
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
I'm hoping this is the right thread to ask this question, but I am looking for a way to conditional format each row individually dependent on whether or not there is a value in a single cell corresponding to its individual row. I.e. All of the row 1 is greyed out because K1 contains a date, but if K2 doesn't contain a date then row 2 is not greyed out.
At the moment I've tried playing with 'Applies to' area but because the formula only requires K1 to be filled regardless of the other row's cells and not working out each row's cells individually, it just ends up with all rows greying out because only one cell is filled.
Hopefully this isn't an impossible task and I'm just missing a simple workaround, and I'd preferably love a way while involves not making an individual rule for each row (as that would be quite time consuming!).
Thank you so much!
Let's say you want to apply this to rows 1 to 100.
Select these rows.
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
=$K1<>""
Please note that there is a $ before the column letter K but not before the row number 1.
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
Hi Hans!
Thank you for sending this, unfortunately this doesn't seem to have worked though. I've attached some screenshots to show what's happened (excuse the blobbed out names, I want to show the whole sheet but need to keep people's privacy!).
I went through the steps and used the formula, but it seems to have greyed out the second two rows, which have nothing in their corresponding final column, while Row 1 which has a completion date remains un-greyed out.
The final screenshot is what I am trying to achieve, but this rule is only applicable to Row 1 alone and when I try to apply it through to Rows 2-100, it greys everything out regardless.
I hope this might help solve the issue!
In your previous reply, you mentioned K1, but your screenshots show that you selected rows from row 2 down. So the formula should be $K2<>"".
Also, make sure that empty-looking cells in column K do not contain a space by accident. That would trigger the rule.
- I'm an idiot! Thank you for your help, this has worked perfectly now! 🙂
