SOLVED
Home

Conditional Formatting based on two cells

%3CLINGO-SUB%20id%3D%22lingo-sub-781103%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781103%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20attach%20the%20file.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781093%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781093%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20making%20a%20document%20in%20which%20I%20need%20to%20be%20able%20to%20conditionally%20format%20two%20cells%20based%20on%20whether%20one%20of%20them%20is%20not%20blank%20(so%20it%20has%20been%20filled%20in%20with%20something).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20document%20attached%20so%20I%20need%20to%20conditionally%20format%20b1%20and%20b2%20so%20if%20i%20write%20in%20one%20of%20them%20the%20yellow%20color%20disappears.%20Same%20goes%20for%20b3%20and%20b4%20with%20the%20blue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBailey100%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-781093%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-781124%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2Cit%20won't%20allow%20me%20to%20upload%20the%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20Bailey100%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781144%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20replying%2C%20click%20on%20below%20icon%20to%20attach%20a%20file.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125047i663211A041A0D041%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781166%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781168%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781168%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20wont%20upload%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781172%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781172%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%2C%20uploading%20doesn't%20work%20now%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781214%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781214%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20select%20the%20column%20or%20cells%20you%20want%20to%20apply%20conditional%20formatting%20then%20click%20on%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConditional%20Formatting%20--%26gt%3B%20Highlight%20Cells%20Rules%20--%26gt%3B%20More%20Rules%20--%26gt%3B%20Format%20Only%20Cells%20With%20--%26gt%3B%20Blanks%20--%20%26gt%3B%20Apply%20desired%20formatting.%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%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125054i6098CC5EFE82280C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125055i6926F01CB3B725E0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781251%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%20for%20if%20I%20want%20to%20conditionally%20format%20one%20cell%20based%20on%20the%20value%20in%20that%20specific%20cell%2C%20however%20I%20want%20a%20conditional%20format%20that%20is%20based%20on%20two%20cells.%20So%20for%20example%20if%20there%20is%20a%20blank%20in%20either%20cell%20b1%20or%20b2%20then%20both%20those%20cells%20with%20remain%20red%2C%20however%20if%20i%20type%20in%20b1%20OR%20b2%20the%20red%20colour%20is%20removed%20in%20b1%20AND%20b2.%20How%20do%20i%20do%20this%20please%3F%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-781266%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781266%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20you%20can%20do%20conditional%20formatting%20based%20on%20two%20cells.%20Please%20apply%20below%20in%20Cell%20B1%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125060i0EC076D41A1D52EB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20this%20in%20B2%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125062iB13349FEAB508792%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781437%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781437%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20this%20works%20for%20blanks%20in%20both%20but%20if%20you%20input%20a%20number%20or%20text%20into%20one%20of%20the%20cells%20the%20format%20does%20not%20clear%2C%20until%20you%20have%20wrote%20in%20both.%20Do%20you%20know%20how%20to%20solve%20this%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%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-781476%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781476%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20one%20rule%20with%20the%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DLEN(B1%26amp%3BOFFSET(B1%2CMOD(ROW()%2C2)*2-1%2C0))%3D0%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eapplied%20to%20entire%20column%20B%20(starting%20from%20cell%20B1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781665%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781665%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3EThanks%2C%20is%20it%20not%20possible%20to%20do%20it%20for%20selected%20sells%20such%20as%20maybe%20b1%20and%20b2%2C%20as%20i%20need%20a%20separate%20one%20for%20b3%20and%20b4%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781740%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20based%20on%20two%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781740%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20the%20rule%20to%20B1%3AB2%20only%20and%2C%20if%20you%20need%20another%20colour%20for%20the%20pair%20B3%3AB4%2C%20create%20one%20more%20rule%20applying%20to%20B3%3AB4%20with%20similar%20formula%2C%20only%20change%20B1%20on%20B3%20in%20it.%20Or%2C%20alternatively%2C%20create%20the%20rule%20for%20B1%3AB2%2C%20after%20that%20apply%20by%20Format%20Painter%20to%20B3%3AB4%20and%20change%20colour%20for%20this%20pair%20rule.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bailey100
Contributor

Hi,

 

I'm making a document in which I need to be able to conditionally format two cells based on whether one of them is not blank (so it has been filled in with something).

 

Here is a document attached so I need to conditionally format b1 and b2 so if i write in one of them the yellow color disappears. Same goes for b3 and b4 with the blue.

 

Many thanks,

 

Bailey100

 

13 Replies

@Bailey100 

 

Please attach the file.

Thanks

@tauqeeracma 

 

Hi,it won't allow me to upload the file.

 

Thanks, Bailey100

@Bailey100 

 

While replying, click on below icon to attach a file.

clipboard_image_0.png

@tauqeeracma 

 

It wont upload?

@tauqeeracma , uploading doesn't work now

@Bailey100 

 

First select the column or cells you want to apply conditional formatting then click on :

 

Conditional Formatting --> Highlight Cells Rules --> More Rules --> Format Only Cells With --> Blanks -- > Apply desired formatting.

 

clipboard_image_0.png

clipboard_image_1.png

@tauqeeracma 

 

This works for if I want to conditionally format one cell based on the value in that specific cell, however I want a conditional format that is based on two cells. So for example if there is a blank in either cell b1 or b2 then both those cells with remain red, however if i type in b1 OR b2 the red colour is removed in b1 AND b2. How do i do this please?

 

 

@Bailey100 

 

Yes you can do conditional formatting based on two cells. Please apply below in Cell B1:

clipboard_image_0.png

 

And this in B2

clipboard_image_0.png

Thanks

 

@tauqeeracma 

 

Hi, this works for blanks in both but if you input a number or text into one of the cells the format does not clear, until you have wrote in both. Do you know how to solve this,

 

Thanks!

 

 

@Bailey100 

You may use one rule with the formula

=LEN(B1&OFFSET(B1,MOD(ROW(),2)*2-1,0))=0

applied to entire column B (starting from cell B1)

@Sergei Baklan 

 

Thanks, is it not possible to do it for selected sells such as maybe b1 and b2, as i need a separate one for b3 and b4?

Solution

@Bailey100 

You may apply the rule to B1:B2 only and, if you need another colour for the pair B3:B4, create one more rule applying to B3:B4 with similar formula, only change B1 on B3 in it. Or, alternatively, create the rule for B1:B2, after that apply by Format Painter to B3:B4 and change colour for this pair rule.