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
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
- Hi! This formula is great - thank you! I am trying to have an entire row shaded like this formula does, but instead of it being shaded when a certain value is entered (such as shipped), I want it to shade the row when the number value is greater than 5. Is there a way to do this by chance?
Could you please illustrate the question with small sample file - it's not clear where is the number, where are values as shipped, do you use range or table
That worked! Thank you so much!Yury Tokarev
If you would allow me one more question Yury Tokarev -
What if, rather than the cell containing a specific word to make the row highlight, just having ANY text typed into the cell will cause the row to highlight? Is there a way to create a rule for that?
(It has been brought to my attention that there are occasionally circumstances when something has shipped, but the row shouldn't be highlighted. I want to instead program the rule to highlight when I type in the date it was received or the name of the person who signed for it, because there is no circumstance when that wouldn't necessitate the row being highlighted.)
I cannot thank you enough!
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.
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!
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!
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! 🙂
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!
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
Make sure that the formula refers to B1, and not to $B$1 or B$1
SergeiBaklan Yes, thanks HansVogelaar your advice worked! I took of the $ from the formula source
Thanks all
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!
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?
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
AUSTXCHICK thank you so much. saved me a lot of aggro!
- 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?
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.- Worked perfectly. Thank you!
