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!

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!

It is a little non-intuitive....

Formula:=$A2=TODAY() and 

Applies to =$A$2:$Z$350 

adds the formatting to the row (e.g., A24:Z24), while 

Formula:=A2=TODAY() and 

Applies to =$A$2:$Z$350 

will ONLY add formatting to the A column cell because the A column cell is the only cell with a date in it. 

AUSTXCHICK 

I created a conditional format for a range of cells to highlight if the cell contents for cells outside of the range match specific text. The formula is =$T2=\"YES\" and the range is =$A$2:$N$500. My problem is one row is highlighting when the value is \"NO\" but all other rows are not highlighting with a \"NO\" value. How can I troubleshoot that? Here are some screenshots to help. I have some CF statement selected to Stop if true. Also, one cell in that same row does not highlight to make it even more interesting.

Screenshots:

All rules with full range selected

All rules with just problematic row selected

All rules with just the one cell that does not highlight in the problematic row

Cell T20 = \"NO\" therefore the row (A20:N20) should not highlight green, which it is. Furthermore, cell K20 is not highlighted. All other rows where the contents of column T = \"NO\" are not highlighted within the range.

HELP?!?!?

Thanks

Tim

TimDT 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

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!

NatalieHarvey 

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.

HansVogelaar 

Hi - hopefully it is ok if I ask you the same question. I am not sure if the other user is still active. It is below. 

Thanks!

Hey there, 

Your solutions are awesome! Could you help me understand why my last row keeps being excluded from my conditional formatting? Even if I add all rows $J:$J a specified number of rows $1:$3000, it keeps excluding the last row when I adjust the sorting. For reference, I am conditionally formatting columns A through J based on the text in column K.

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?

AUSTXCHICK   thank you so much.  saved me a lot of aggro!

AUSTXCHICK 

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

SergeiBaklan 

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\"

Ebony_T_0524 

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.

SergeiBaklan 

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!

AimeeW1995 

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.

HansVogelaar 

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!

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

Charlie585 

Like this?

SergeiBaklan Yes, thanks HansVogelaar your advice worked! I took of the $ from the formula source

Thanks all

Charlie585 

Make sure that the formula refers to B1, and not to $B$1 or B$1

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.

GMcArthur 

\"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!

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