Conditional formatting for entire row based on data in one cell

[AUSTXCHICK]

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!

[#2]

@AYC94 

Not sure I understood, is it possible to provide small sample file with manually indicated desired result?

[#3]

@SergeiBaklan 

I found this site helpful: https://www.extendoffice.com/documents/excel/2525-excel-highlight-row-if-cell-blank-contains-value-t... 

[#4]

@Yury Tokarev 

The formula works but when I copy the formatting to another set of data the $D1 reference keeps referring to column D. If I change the formula to D1 in that case the condition formatting works only for 1 column of the row. 

Is there a way to copy the condition formatting and ensure it will work for the new cell reference ?

[#5]

@srvikas 

I'm not an expert at all, but you might find this site helpful: https://trumpexcel.com/copy-conditional-formatting-another-cell-excel/ 

[#6]

Hi..what formula should i use if the status cell contains the date? I just want the entire row to be highlighted based on the current date for easy recognition

[#7]

Can this be dynamic? Let's say I add a line 51... do I have to play with conditionnal formatting formula?

[#8]

@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!

[#9]

@Yury Tokarev 

Hello!

I have followed the steps as written in your post but it is not working for me. 

=$B1=”Inactive” to highlight all rows containing the word "inactive" in column B.

What am I doing wrong?

[#10]

@AmnaSK 

=$B1="Inactive"

You can try this rule for conditional formatting. The difference are the quotation marks. This " instead of that ”.

[#11]

@OliverScheurich IT WORKED!

Thank you so much! 

[#12]

Thank you very much, works perfectly with additional code from Sergei Baklan :

Instead of 

=$D1="Shipped"

for the rule formula you may use

=LEN($D1)

when i input payment amount in column J it will fill entire row with light green colour

Thanks @Yury Tokarev , @SergeiBaklan 

[#13]

@SergeiBaklan

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.

[#14]

@Pradee91221 

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!

[#15]

@HansVogelaar,
Thank You Very Much to help me, it worked perfectly.

[#16]

@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.

[#17]

@Ruben_at_Fernwood 

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.

[#18]

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. 

Ruben

[#19]

@Ruben_at_Fernwood 

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.

[#20]

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

[#21]

@SergeiBaklan 

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! 🙂