Forum Discussion
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
I created a conditional format for a range of cells to highlight if the cell contents for cells outside of the range match specific text. The formula is =$T2="YES" and the range is =$A$2:$N$500. My problem is one row is highlighting when the value is "NO" but all other rows are not highlighting with a "NO" value. How can I troubleshoot that? Here are some screenshots to help. I have some CF statement selected to Stop if true. Also, one cell in that same row does not highlight to make it even more interesting.
Screenshots:
All rules with full range selected
All rules with just problematic row selected
All rules with just the one cell that does not highlight in the problematic row
Cell T20 = "NO" therefore the row (A20:N20) should not highlight green, which it is. Furthermore, cell K20 is not highlighted. All other rows where the contents of column T = "NO" are not highlighted within the range.
HELP?!?!?
Thanks
Tim
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- Hi Hans,
I'm new to this community. I don't see a way to attach my sample file to this post. Is there a means for me to share it with you? There are formulas that won't be captured by a screenshot.
Thanks,
Tim
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!
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.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.
- Hello,
This a great learning thread.
I have a similar problem to solve. I have ColumnA of char data (ABC for example). I have another ColumnB of unique char data as reference. I want to highlight each row that contains a match with anyone one of the ColumnB reference values. I assume ColumnB needs to go on a separate tab?
Thank you!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?
AUSTXCHICK thank you so much. saved me a lot of aggro!
Take a look at this 5 minute video instead of searching this page for half an hour :
https://youtu.be/XHT4paRaY4g Excel Conditional Formatting with Formula | How to Get it RIGHT Every Time
Along the same lines- Is there a way to highlight rows that contain sales and other words such as "sales- food store 1, sales- food store 2".
I still need all the rows with the word sales highlighted. I have no rows with ONLY the word "sales"
Let's say you want to format rows from row 2 to row 100 that contain "sales" with possibly other text in column D.
Select rows 2 to 100.
The active cell in the selection should be in row 2, for example A2.
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=ISNUMBER(SEARCH("sales", $D2))
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.- Sorry to pile on with new question, but you all have such great responses! I'm using =LEFT(FILTER(range,criteria,"")10) to list only the first 10 characters from a filtered list of employees. I then want to conditionally format $B1:$J800 when the results of $C1 ="Text". If I use CF when text contains "Text", it will successfully highlight the cells containing "Text", but won't highlight the entire row, even if applies to $B1:$J800. If I use a formula to CF, it's never true because the cell doesn't ="Text", it equals the Filter formula. Any advice?
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!
SergeiBaklan I hope you can help me, I have created a conditional rule eg. if A1 is higher in value than B1 it should highlight green but if its lower than it should highlight red, I know want to drag it down across the entire sheet but what's happening is that all formulas are based on the same B1 instead I need each row of A to compare to the same row in B (so A5 compares to B5), I have tried adding :$A$700 but that didn't work either - Please help
SergeiBaklan Yes, thanks HansVogelaar your advice worked! I took of the $ from the formula source
Thanks all
Make sure that the formula refers to B1, and not to $B$1 or B$1
SergeiBaklan
Help please 🙂
I have this data below:When the status in column D is updated, I would like the back ground from C17:E20 to fill that light purple colour.
Any assistance would be greatly appreciated."updated" means changing from any one value to any other one. Afraid that doesn't work. You may assign rules for predefined set of values. One rule for one value.
SergeiBaklan Okay, thanks so much for your help anyway!
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
- Worked, thank you!
Yury Tokarev I am wanting to do something very similar to the OP. The difference is I want a whole row to change color when any date is entered into the first cell of the row (A). Can you help me with that?
Thanks,
Craig
Yury Tokarev I tried this, but only cell containing formula and 1st cell in the row is getting highlighted.
