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]

@pov_77 

That's better to discuss with sample file. You may try something like

[#3]

@SergeiBaklan 

Thanks for the prompt response! I wanted to share my file, but can't figure out how to attach it 😞 

[#4]

@pov_77 

If you cannot attach a sample file, you can make it available through OneDrive, Google Drive, Dropbox or similar

[#5]

@SergeiBaklan 

Sergei,

Is there a way to change the entire row color if the text is the same in one cell, then to change the a different entire row(s) when the text changes to another word to a different random color?

I want to have an entire spread sheet become color coded based on a program name in column F. I don’t care what the colors are.  
thank you,

Thom

[#6]

@Thom_BeschThomBesch 

You'd have to create a different rule for each word.

For example, if you want to color a row if column F contains Excel:

• Select all cells. The active cell in the selection should be in row 1.
• On the Home tab of the ribbon, select Conditional Formatting > New Rule...
• Select 'Use a formula to determine which cells to format'.
• Enter the formula =$F1="Excel"
• Click Format...
• Activate the Fill tab.
• Select a color.
• Click OK, then click OK again.
• Repeat for other words.

[#7]

I think this is the proper discussion thread for my question:
I need to highlight all rows that are above a cell with a specific value. For example, cell A356 contains the unique word "total" in Column A. I need rows A1:A356 highlighted. I assume it is a simple Find function in conditional formatting for the 1st rule. But I can't figure out how to highlight the range above after it finds the word. I can't use a rule specific to highlighting from rows 1 to 356 because the word changes rows for different sheets/workbooks. I would need the 2nd rule to work as part of a script where it finds the word and highlights all rows above when the range of rows is variable... Also, it can't highlight the rows below the word, so I can't just do a full column rule.

Needs to be functional as a script in Excel 365 😞
Thanks!

[#8]

@BMayldry 

Not sure I understood the logic. For such sample

formula for conditional formatting rule could be

=( ROW($C3) > ( XMATCH("start", $B$3:$B$12) + ROW($B$3) - 1 )) * ( ROW($C3) < ( XMATCH("total", $B$3:$B$12) + ROW($B$3) -1 ))

[#9]

@SergeiBaklan 

Yes, that's perfect. I didn't think of setting a 'start' value cell for the upper limit, I was just trying to highlight everything above 'total,' but I can make it work with your suggestion. Many thanks! 

[#10]

@BMayldry , you are welcome

[#11]

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

[#12]

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

[#13]

@SergeiBaklan Okay, thanks so much for your help anyway!

[#14]

Many Thanks, @Yury Tokarev. Tried for a number value for one of my BAU, as it worked, as well. I was about to post this in the community, however this suggestion popped -up and I had a look into it and applied. 

Thanks @AUSTXCHICK , for bringing this up.

[#15]

@Yury Tokarev Hello, does this work in Excel online also?

[#16]

Yes it does. The interface has been improved, and it is now possible to setup the conditional formattting in one place. Here are the steps:
1. Select Home>Styles>Conditional Formatting>New Rule
2. Enter $1:$50 into the box 'Apply to range"
3. Select 'Formula' in the 'Rule Type drop-down' box
4. Enter $D1="Shipped" in the field 'Format all values where this formula is true'
5. Select the desired formatting option or a custom format and click 'Done'.

[#17]

@SergeiBaklan I'm seeking to sum dollar values in one column where the conditional formatting rule of "cell that contains "insurance"" in another column. Was first using the conditional formatting of highlighting cells that contain "insurance" then trying to sum another column in that same row. my hangup is that the cell is not an equal to rather 'contains' a word. Suggestion?

[#18]

@clangenfeld 

Sorry, I didn't catch why do you need conditional formatting to sum some cells with condition. You may use something like

=IF( <some cell> = "insurance", SUM(myRange), "")

or like

[#19]

@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

[#20]

@Charlie585 

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

[#21]

@Charlie585 

Like this?