SOLVED
Home

Conditional formatting for entire row based on data in one cell

%3CLINGO-SUB%20id%3D%22lingo-sub-779037%22%20slang%3D%22en-US%22%3EConditional%20formatting%20for%20entire%20row%20based%20on%20data%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779037%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20all%20cells%20in%20a%20row%20to%20highlight%20a%20certain%20color%20if%20the%20data%20in%20one%20cell%20contains%20a%20specific%20word.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20specifically%20want%20is%20for%20an%20entire%20row%20to%20turn%20grey%20if%20the%20status%20cell%20contains%20the%20word%20%22SHIPPED.%22%20I%20know%20how%20to%20make%20that%20specific%20cell%20highlight%20the%20color%20I%20want%2C%20but%20not%20the%20entire%20row%20of%20the%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-779037%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779096%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20based%20on%20data%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384283%22%20target%3D%22_blank%22%3E%40AUSTXCHICK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20create%20the%20rule%20with%20the%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DA1%3D%22Shipped%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20apply%20it%20to%20your%20entire%20range%2C%20let%20say%20%24A%241%3A%24Z%24200%3C%2FP%3E%0A%3CP%3EAll%20rows%20with%20%22Shipped%22%20will%20be%20in%20desired%20color.%3C%2FP%3E%0A%3CP%3EA1%20in%20formula%20shall%20be%20top%20left%20cell%20of%20your%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779304%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20based%20on%20data%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779304%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20that%20D%20is%20the%20column%20containing%20the%20status%20cells%2C%20and%20you%20wish%20to%20apply%20conditional%20formatting%20to%20rows%201%3A50%2C%20here%20are%20the%20steps%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%26nbsp%3B%20Select%20any%20cell%20in%20row%201%3C%2FP%3E%3CP%3E2.%20Go%20to%20'Conditional%20Formatting%26gt%3BNew%20Rule%26gt%3BUse%20a%20formula%20to%20determine%20which%20cells%20to%20format'%3C%2FP%3E%3CP%3E3.%20In%20the%20formula%20field%20paste%26nbsp%3B%3CFONT%3E%3D%24D1%3D%22Shipped%22%2C%20set%20the%20required%20format%20and%20click%20'OK'%3C%2FFONT%3E%3C%2FP%3E%3CP%3E4.%20Go%20to%20'Conditional%20Formatting%26gt%3BManage%20Rules'%20and%20locate%20the%20rule%20you%20have%20just%20created%3C%2FP%3E%3CP%3E5.%20In%20the%20field%20'Applies%20to'%20paste%20'%3CFONT%3E%3D%241%3A%2450'%2C%20or%20just%20select%20the%20required%20rows%2C%20starting%20from%20row%201%2C%20then%20click%20'OK'.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EIf%20you%20type%20the%20word%20'SHIPPED'%20in%20any%20cell%20of%20the%20column%20D%20(rows%201%20to%2050)%2C%20the%20entire%20row%20will%20be%20conditionally%20formatted%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EHope%20this%20helps%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EYury%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779345%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20based%20on%20data%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779345%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20worked!%20Thank%20you%20so%20much!%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F15833%22%20target%3D%22_blank%22%3E%40Yury%20Tokarev%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779601%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20based%20on%20data%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779601%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20would%20allow%20me%20one%20more%20question%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F15833%22%20target%3D%22_blank%22%3E%40Yury%20Tokarev%3C%2FA%3E%26nbsp%3B%20-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20if%2C%20rather%20than%20the%20cell%20containing%20a%20specific%20word%20to%20make%20the%20row%20highlight%2C%20just%20having%20ANY%20text%20typed%20into%20the%20cell%20will%20cause%20the%20row%20to%20highlight%3F%20Is%20there%20a%20way%20to%20create%20a%20rule%20for%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(It%20has%20been%20brought%20to%20my%20attention%20that%20there%20are%20occasionally%20circumstances%20when%20something%20has%20shipped%2C%20but%20the%20row%20shouldn't%20be%20highlighted.%20I%20want%20to%20instead%20program%20the%20rule%20to%20highlight%20when%20I%20type%20in%20the%20date%20it%20was%20received%20or%20the%20name%20of%20the%20person%20who%20signed%20for%20it%2C%20because%20there%20is%20no%20circumstance%20when%20that%20wouldn't%20necessitate%20the%20row%20being%20highlighted.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20thank%20you%20enough!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-780039%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20based%20on%20data%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780039%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384283%22%20target%3D%22_blank%22%3E%40AUSTXCHICK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%20of%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3D%24D1%3D%22Shipped%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efor%20the%20rule%20formula%20you%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DLEN(%24D1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-829645%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20defined%20cells%20in%20a%20row%20based%20on%20data%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-829645%22%20slang%3D%22en-US%22%3EI%20used%20Yuri%20Tokarev's%20directions%20to%20apply%20%22conditional%20formatting%20for%20entire%20row%20based%20on%20data%20in%20one%20cell%22%2C%20but%20am%20wondering%20if%20there%20is%20a%20way%20to%20apply%20conditional%20formatting%20for%20a%20consecutive%20set%20of%20cells%20(i.e.%2C%20B%20thru%20N)%20in%20a%20row%20based%20on%20data%20in%20one%20cell%20in%20that%20row%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779122%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20based%20on%20data%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779122%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20response%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20I'm%20a%20total%20novice%2C%20but%20when%20I%20followed%20your%20instructions%20(I%20think)%2C%20it%20only%20highlighted%20the%20cell%20that%20contained%20the%20word%20%22shipped%22%20instead%20of%20the%20entire%20row.%20How%20can%20I%20make%20each%20individual%20row%20turn%20grey%20when%20the%20cell%20in%20column%20G%20for%20that%20row%20contains%20the%20word%20%22shipped%3F%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124917i1241577F80F2392D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22excel%20question.PNG%22%20title%3D%22excel%20question.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-830227%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20defined%20cells%20in%20a%20row%20based%20on%20data%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830227%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400535%22%20target%3D%22_blank%22%3E%40Lady_Di%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20use%20exactly%20the%20same%20formula%20for%20the%20rule%2C%20and%20apply%20it%20to%20the%20range%20%24B%241%3A%24N%241000%20or%20so.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837166%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20defined%20cells%20in%20a%20row%20based%20on%20data%20in%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837166%22%20slang%3D%22en-US%22%3EWorked%20perfectly.%20Thank%20you!%3C%2FLINGO-BODY%3E
AUSTXCHICK
New Contributor

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!

9 Replies

@AUSTXCHICK 

You may create the rule with the formula

=A1="Shipped"

and apply it to your entire range, let say $A$1:$Z$200

All rows with "Shipped" will be in desired color.

A1 in formula shall be top left cell of your range.

Thank you for your response @Sergei Baklan 

 

Sorry I'm a total novice, but when I followed your instructions (I think), it only highlighted the cell that contained the word "shipped" instead of the entire row. How can I make each individual row turn grey when the cell in column G for that row contains the word "shipped?"

 

Thank you so much!

 

excel question.PNG

 

Solution

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

 

That worked! Thank you so much!@Yury Tokarev 

 

If you would allow me one more question @Yury Tokarev  -

 

What if, rather than the cell containing a specific word to make the row highlight, just having ANY text typed into the cell will cause the row to highlight? Is there a way to create a rule for that?

 

(It has been brought to my attention that there are occasionally circumstances when something has shipped, but the row shouldn't be highlighted. I want to instead program the rule to highlight when I type in the date it was received or the name of the person who signed for it, because there is no circumstance when that wouldn't necessitate the row being highlighted.)

 

I cannot thank you enough!

 

 

@AUSTXCHICK 

Instead of 

=$D1="Shipped"

for the rule formula you may use

=LEN($D1)
I used Yuri Tokarev's directions to apply "conditional formatting for entire row based on data in one cell", but am wondering if there is a way to apply conditional formatting for a consecutive set of cells (i.e., B thru N) in a row based on data in one cell in that row?

@Lady_Di 

Yes, use exactly the same formula for the rule, and apply it to the range $B$1:$N$1000 or so.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies