Conditional formatting a row

%3CLINGO-SUB%20id%3D%22lingo-sub-1462187%22%20slang%3D%22en-US%22%3EConditional%20formatting%20a%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1462187%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20format%20a%20whole%20row%20based%20on%20one%20cell%20in%20that%20row.%20If%20the%20cell%20has%20a%20negative%20value%20then%20it%20is%20filled%20green%2C%20and%20if%20it%20has%20a%20positive%20value%20it%20is%20filled%20purple.%20I%20would%20like%20this%20fill%20to%20apply%20to%20the%20full%20row%2C%20but%20I%20am%20having%20a%20difficult%20time%20figuring%20out%20how.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mkrpavel_0-1592172061137.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F198655iA02F392C4DAC57C5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22mkrpavel_0-1592172061137.png%22%20alt%3D%22mkrpavel_0-1592172061137.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1462187%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1462250%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20a%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1462250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F699068%22%20target%3D%22_blank%22%3E%40mkrpavel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Select%20whole%20data%20range%20where%20you%20want%20to%20apply%20the%20formatting%2C%20e.g.%20A2%3AE100%3C%2FP%3E%3CP%3E2.%20Conditional%20Formatting%2FNew%20Rules%2FUse%20a%20formula%20to%20determine%20which%20cell%20to%20format%3C%2FP%3E%3CP%3E3.%20Enter%20the%20following%20formula%20exactly%20into%20the%20formula%20field%3A%3C%2FP%3E%3CP%3E%3D%24E2%26lt%3B0%3C%2FP%3E%3CP%3Eand%20click%20Format%20and%20choose%20Fill%20%3D%20Green%20color%3C%2FP%3E%3CP%3E4.%20Do%20the%20same%20steps%201-3%20but%20enter%20new%20formula%3A%20%3D%20%24E2%26gt%3B%3D0%20and%20choose%20Format%20%3D%20Purple%20color%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20do%20all%20above%20correctly%2C%20when%20you%20go%20to%20Conditional%20Formatting%2FManage%20Rules%2FShow%20formatting%20rules%20for%20this%20worksheet%2C%20you%20should%20see%20the%20below%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ConditionalFormat.png%22%20style%3D%22width%3A%20624px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F198681iA20E714628F71A18%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22ConditionalFormat.png%22%20alt%3D%22ConditionalFormat.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1462325%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20a%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1462325%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F699068%22%20target%3D%22_blank%22%3E%40mkrpavel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20should%20try%20and%20use%20the%20option%20of%20%22use%20a%20formula%20to%20determine%20which%20cells%20to%20format%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20use%20these%20formulae%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%24E2%26gt%3B0%20for%20positive%20values%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3E%3D%24E2%26lt%3B0%20for%20negative%20values%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20ensure%20that%20the%20%22applies%20to%22%20covers%20the%20part%20of%20the%20row%20you%20will%20like%20to%20format%20e.g%20%3D%24A%242%3A%24E%2422.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20sample%20file%20for%20your%20perusal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello,

I am trying to format a whole row based on one cell in that row. If the cell has a negative value then it is filled green, and if it has a positive value it is filled purple. I would like this fill to apply to the full row, but I am having a difficult time figuring out how.

mkrpavel_0-1592172061137.png

 

2 Replies
Highlighted

@mkrpavel 

1. Select whole data range where you want to apply the formatting, e.g. A2:E100

2. Conditional Formatting/New Rules/Use a formula to determine which cell to format

3. Enter the following formula exactly into the formula field:

=$E2<0

and click Format and choose Fill = Green color

4. Do the same steps 1-3 but enter new formula: = $E2>=0 and choose Format = Purple color

 

If you do all above correctly, when you go to Conditional Formatting/Manage Rules/Show formatting rules for this worksheet, you should see the below:

ConditionalFormat.png

Highlighted

Hi @mkrpavel 

 

You should try and use the option of "use a formula to determine which cells to format"

 

Then use these formulae

 

=$E2>0 for positive values

or

=$E2<0 for negative values

 

Also ensure that the "applies to" covers the part of the row you will like to format e.g =$A$2:$E$22.

 

I have attached a sample file for your perusal.

 

Cheers