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
SergeiBaklan That worked great, thanks!
elgar65 , you are welcome
- Thank you @Sergei_Baklan.
My googling over the years has found this is a known problem with CF--I just found the INDIRECT and thought it might work. I appreciate you taking a look at it.
I also tried putting the indirect in the range since that is actually the part that gets broken, but the CF dialogue resets it immediately.
Thanks again Thank you for the update.
Afraid INDIRECT() won't help in such scenario. In your case that could be
=INDIRECT(ADDRESS(1,COLUMN()))=TODAY()+6-MOD(TODAY()+2, 7)but that problem is that when you drag the row (or any range) you actually cut such row and put it into new place. Cutting the row means you remove everything from that place. Not only values but formatting as well. CF follows such instruction and removes formatting from such row splitting the range to apply on two parts, before and after the row.
INDIRECT() could help with copy/pasting to keep references, and perhaps mainly on previous versions.
At least I don't know workaround for your case, sorry.
Thank you SergeiBaklan
I am using Excel 2019.
I notice that the URL I posted did not make it through. Here it is again, on preventing CF rules from multiplying. That is where I got the idea for INDIRECT from.
Here is a sample workbook, zipped in dropbox.
You will see on the right hand side that this week's column has blue lines on both sides. That is a cell format to help me not get lost in the all the coloured boxes.
But if I drag or delete a row, it breaks the CF and changes the range it applies to--say if you drag row 31 up a row.
Thanks for any help or thoughts you might have here.
RubenCF 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.
- 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. SergeiBaklan Is there a way to format the fill color of rows based on the information in Column A?
for example: I have a list of people in column A (lets say A1, A2, and A6) and dates I emailed them in column B (B1:B6). Now I have emailed some people multiple times so there are multiple rows beneath those names (but only info in column B and none in column A since that would be redundant to add the name again). So the names are in A1, A2, and A6, but dates in each of the B Columns.
I would like the filled colors to switch back and forth between gray and white each time a new name is entered into column A... while keeping the same filled color in the rows under the name without anything entered in column A.
See photo (I selected each row separately to fill but would like it to auto fill).
Thanks!
Not sure I understood, is it possible to provide small sample file with manually indicated desired result?
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.
