Home

Conditional formatting with adjacent cells

%3CLINGO-SUB%20id%3D%22lingo-sub-112578%22%20slang%3D%22en-US%22%3EConditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-112578%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello!%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20continously%20expanding%20rows.%20I%20want%20to%20create%20one%20column%20that%20has%20conditonal%20formatting%20with%20its%20corresponding%20adjacent%20cell.%20The%20format%20painter%20doesn't%20work%20because%20it%20will%20not%20allow%20for%20relative%20formatting%2C%20only%20absolute.%20The%20only%20solution%20I%20see%20is%20to%20manually%20enter%20the%20formula%20into%20each%20cell.%20This%20is%20not%20practical.%3C%2FP%3E%3CP%3EIs%26nbsp%3Bthere%20and%20way%20to%20reuse%20the%20same%20formula%20relative%20to%20the%20cells%20being%20formatted%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20291px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F21461i0BDE978FCACDF116%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-112578%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-340053%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-340053%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%2C%20I%20didn't%20catch%20which%20formula%20you%20try%20to%20use.%20Please%20check%20attached%20sample%20for%20Grade%20Scale.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338967%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338967%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20attempted%20this%20solution%20but%20with%20the%20formula%20using%20%24J%243%20as%20an%20absolute%20reference%20in%20the%20Conditional%20Formatted%20formula%2C%20how%20can%20it%20be%20applied%20to%20the%20cells%20beneath%20such%20as%20J%244%24%2C%20J%245%24%2C%20etc%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20here%20is%20the%20range%20of%20the%20entire%20column%20I%20wish%20to%20format%20following%20the%20formula%20in%20cell%20P4.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20629px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F75048i41092241A9CB428D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20order%20to%20apply%20the%20same%20formatting%20to%20all%20cells%20in%20the%20same%20column%2C%20the%20formula%20would%20have%20to%20be%20relative%20such%20that%20the%20formula%20would%20change%20depending%20on%20the%20cell%20being%20formatted.%20However%2C%20I%20keep%20getting%20this%20error%20when%20attempting%20to%20use%20a%20relative%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20over%20come%20this%20challenge%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293336%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293336%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20welcome.%20That's%20not%20my%20idea%2C%20if%20you%20google%20perhaps%20you%20find%20the%20post%20from%20the%20person%20who%20originally%20suggested%20the%20solution.%20But%20I%20lost%20the%20link%20and%20don't%20remember%20who%20that%20was.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293174%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293174%22%20slang%3D%22en-US%22%3E%3CP%3EI%20spent%20the%20last%2048%20hours%20periodically%20trying%20to%20solve%20this%20problem%20and%20finding%20that%20same%20ineffective%20formula%20the%20original%20poster%20referenced%2C%20over%20and%20over.%20You%20appear%20the%20be%20the%20only%20person%20on%20Earth%20who%20knew%20how%20to%20do%20that%2C%20and%20thank%20you%20SO%20MUCH.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-112863%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-112863%22%20slang%3D%22en-US%22%3E%3CP%3EJames%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20welcome.%20When%20you%20are%20ready%20with%20new%20question%20better%20if%20you%20attach%20small%20sample%20file%2C%20will%20be%20easier%20to%20discuss.%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-112857%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-112857%22%20slang%3D%22en-US%22%3E%3CP%3EWorks%20great!!%20Thank%20you.%3C%2FP%3E%3CP%3EThere%20will%20be%20an%20issue%20in%20the%20future%2C%20as%20this%20is%20going%20to%20be%20used%20as%20a%20continous%20log%2C%20when%20rows%20are%20added%2C%20that%20column%20will%20need%20to%20be%20%22repainted%22%20from%20the%20top%20cell%2C%20for%20the%20equation%20to%20be%20consistant.%20-But%2C%20that%20is%20another%20conversation%2C%20for%20another%20day.%3C%2FP%3E%3CP%3EThank%20you%20very%20much!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-112846%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-112846%22%20slang%3D%22en-US%22%3E%3CP%3EForgot%20to%20say%2C%20with%20this%20you%20shall%20apply%20your%20rule%20only%20to%20first%20cell%20in%20the%20range%20(I3)%2C%20aplly%20to%20next%20ones%20by%20format%20painter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-112838%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-112838%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20James%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20see.%20The%20workaround%20is%20to%20use%20offset%2C%20which%20gives%20the%20reference%20on%20next%20column%20and%20current%20row.%20Not%20sure%20what's%20your%20formula%2C%20could%20be%20like%3C%2FP%3E%3CPRE%3E%3D0.67*OFFSET(%24J%243%2CROW()-3%2C0%2C1%2C1)%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20660px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F21504iC195EBF0C198EF60%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Rules1.JPG%22%20title%3D%22Rules1.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eand%20apply%20it%20to%20your%20entire%20range.%20row()-3%20returns%20first%20row%20in%20your%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-112794%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-112794%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you.%20I%20appreciate%20your%20attempting%20to%20help%20me.%3C%2FP%3E%3CP%3ELet%20me%20better%20explain%20what%20I%20require%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cell%20I3%2C%20i%20want%20to%20use%20the%20conditional%20format%20based%20on%20the%20value%20of%20J3.%3C%2FP%3E%3CP%3EI%20then%20want%20I4%20to%20apply%20the%20same%20formula%20based%20on%20the%20value%20of%20J4.%20The%20same%20for%20I5%20and%20J5%2C%20and%20so%20on.%3C%2FP%3E%3CP%3EI%20can%20use%20the%20format%20painter%26nbsp%3Bto%20apply%20the%20formula%20in%26nbsp%3Bthe%20coulumn%20%22I%22%2C%20but%20it%20will%20apply%20the%20formula%20only%20based%20on%20the%20value%20of%20J3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20provides%20more%20clairity%20of%20the%20situation%2C%20and%20I%20am%20grateful%20for%20the%20help.%20-James%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-112595%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20with%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-112595%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20James%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20rule%20is%20the%20same%20format%20painter%20shall%20work.%20For%20example%2C%20that%20is%20done%20by%20format%20painter%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20545px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F21462iA47C7C83EBA39730%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Rule.JPG%22%20title%3D%22Rule.JPG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20733px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F21463iEC1F07C313FE5921%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22FormatPainter.JPG%22%20title%3D%22FormatPainter.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
James Urbanski
New Contributor

 

Hello!

I have a spreadsheet with continously expanding rows. I want to create one column that has conditonal formatting with its corresponding adjacent cell. The format painter doesn't work because it will not allow for relative formatting, only absolute. The only solution I see is to manually enter the formula into each cell. This is not practical.

Is there and way to reuse the same formula relative to the cells being formatted?

Capture.PNG

Thank you.

10 Replies

Hi James,

 

If the rule is the same format painter shall work. For example, that is done by format painter:

Rule.JPGFormatPainter.JPG

 

Thank you. I appreciate your attempting to help me.

Let me better explain what I require:

 

In cell I3, i want to use the conditional format based on the value of J3.

I then want I4 to apply the same formula based on the value of J4. The same for I5 and J5, and so on.

I can use the format painter to apply the formula in the coulumn "I", but it will apply the formula only based on the value of J3.

 

I hope this provides more clairity of the situation, and I am grateful for the help. -James

Hi James,

 

I see. The workaround is to use offset, which gives the reference on next column and current row. Not sure what's your formula, could be like

=0.67*OFFSET($J$3,ROW()-3,0,1,1)

Rules1.JPG

and apply it to your entire range. row()-3 returns first row in your range.

Forgot to say, with this you shall apply your rule only to first cell in the range (I3), aplly to next ones by format painter

Works great!! Thank you.

There will be an issue in the future, as this is going to be used as a continous log, when rows are added, that column will need to be "repainted" from the top cell, for the equation to be consistant. -But, that is another conversation, for another day.

Thank you very much!!

James,

 

You are welcome. When you are ready with new question better if you attach small sample file, will be easier to discuss.

Thank you

I spent the last 48 hours periodically trying to solve this problem and finding that same ineffective formula the original poster referenced, over and over. You appear the be the only person on Earth who knew how to do that, and thank you SO MUCH.

You are welcome. That's not my idea, if you google perhaps you find the post from the person who originally suggested the solution. But I lost the link and don't remember who that was.

I've attempted this solution but with the formula using $J$3 as an absolute reference in the Conditional Formatted formula, how can it be applied to the cells beneath such as J$4$, J$5$, etc?

 

For example, here is the range of the entire column I wish to format following the formula in cell P4. 

image.png

In order to apply the same formatting to all cells in the same column, the formula would have to be relative such that the formula would change depending on the cell being formatted. However, I keep getting this error when attempting to use a relative reference.

 

How do I over come this challenge?

Sorry, I didn't catch which formula you try to use. Please check attached sample for Grade Scale.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 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
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies