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
SergeiBaklan That worked great, thanks!
elgar65 , you are welcome
- SergeiBaklan
You seem to be a master of formatting rows and columns based on single cells! I saw you commenting on a different page as well.
I use border formats to highlight this week's column for a sheet of volunteers I work with. But, when deleting or inserting rows the CF gets messed up.
I found this page on preventing the creation of extra rules by using indirect, which seems relevant. Will this work? Can you help me apply indirect to this situation? Maybe it needs to be applied to the range?
I have the range =$B$1:$GG$98
And the CF formula =B$1=TODAY()+6-MOD(TODAY()+2, 7)
Thank you very much.CF is improved for latest versions of Excel and works better from that point of view. On which Excel you are?
In any case, it's better to add/remove rows on unfiltered data if that's the case.
Not sure INDERECT() helps a lot, which exactly info do you mean? In your CF formula I see no references instead of which we may use INDIECT() if to try.
Perhaps with small sample file it'll be more clear.
Hi, I want to highlight a certain similar reference numbers given for a customer, for example few customers might use the same reference number for an assessed criteria, appreciate if you could instruct me to apply conditional formatting to a single column to get the similar reference numbers to each other highlighted. (there could be few customers with similar reference numbers not just a specific one for all, I need to get such customers who might share the similar reference numbers highlighted). Thanks in advance.
Not sure I understood, is it possible to provide small sample file with manually indicated desired result?
