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
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.
Yury Tokarev thanks for your instructions are simple but accurate and effective and work brilliantly! 😊
Yury Tokarev THANK YOU! You changed my life! 😄 I knew this was possible but never could find super clear instructions to get it done.
I believe I have followed your instructions correctly, but the desired result only works in the first row.
The desired result is that any row from 5 to 500 that contains a qty other than zero in column F be highlighted.
Thank you in advance for your help.
I believe I went into conditional formatting and selected 'use a formula to determine which cells to format'. Then, I put $H1='N'. The $ keeps the column consistent while the row may change. So, in your case, it would be $F5>0. I am not able to troubleshoot this at the moment, but let me know if it works.
Good morning,
Appreciate the guidance you provided on this thread. for some reason, I inputted the instructions and for some reason when I select "No-show" from the drop down on Column " F" the red goes down a row. So it is not highlighting the row that the drop down in in. Please advise and thank you Yury.
Most probably references in formula and in Apply To range are not in sync. For example
we use in formula reference with first row and apply to the range which starts from the second row. If to change on row 2 in formula, it works
I'm trying to do a similar type of formatting where a row is highlighted when the end cell says completed. It works fine for one row however when I add more to the range the entire range is highlighted despite only the first row containing "completed" in the appropriate column. How do I make it so the row is only highlighted if the cell in column E for that row says "completed"
Thanks this is great, is it possible to make it so it only changes the color of the row to a specific column rather than the entire row?
- lifesaver! Thanks a lot!
