SOLVED
Home

How to automatically change cell's background color by a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-668871%22%20slang%3D%22en-US%22%3EHow%20to%20automatically%20change%20cell's%20background%20color%20by%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-668871%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20need%20some%20little%20help%20here%2C%20I'd%20like%20to%20write%20a%26nbsp%3Bformula%26nbsp%3Bto%26nbsp%3Bautomatically%20change%20a%20cell's%20background%20color%20when%20the%20next%20cell's%20content%20(text%20or%20value)%20is%20different%20from%20the%20previous%26nbsp%3Bcell's%20content%2C%20for%20example%20please%20see%20the%20attached%20picture%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20608px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117174iD47161AF131A1A3C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-06-04%20145306.jpg%22%20title%3D%22Annotation%202019-06-04%20145306.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAs%20long%20as%20the%20next%20cell's%20content%20is%20different%20from%20the%20previous%26nbsp%3Bcell's%20content%2C%20the%20next%20cell's%26nbsp%3Bbackground%20color%20will%20be%20changed%26nbsp%3Bautomatically.%20Does%20anyone%20know%20how%20to%20write%20a%20formula%20for%20this%20function%3F%3F%20I%20prefer%20writing%20formula%20instead%20of%20creating%20a%20rule%20because%20by%20this%20I%20can%20copy%20and%20paste%20this%20formula%20to%20apply%20to%20tons%20of%20other%20cells.%20Thank%20you%20sooooo%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-668871%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-669124%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20change%20cell's%20background%20color%20by%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-669124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354709%22%20target%3D%22_blank%22%3E%40vividwhp%3C%2FA%3E%26nbsp%3B%2C%20formulas%20don't%20return%20cell%20properties%20as%20color%2C%20only%20values.%20That%20with%20VBA%20programming.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAt%20the%20same%20time%20the%20rule%20with%20simple%20formula%20could%20be%20applied%20to%20all%20your%20tons%20of%20cells.%20If%20they%20are%20in%20one%20range%20that%20will%20be%20only%20one%20rule%20for%20entire%20range%2C%20otherwise%20approximately%20as%20many%20as%20many%20ranges%20you%20have.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-669255%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20change%20cell's%20background%20color%20by%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-669255%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%2C%20thank%20you%20so%20much%20for%20the%20reply%2C%20i%20see..........%3C%2FP%3E%3CP%3Eso.......could%20you%20please%20quickly%20show%20me%20how%20to%20create%20the%20rule%20for%20this%20function%20for%20a%20%3CSPAN%3Eentire%20range%3C%2FSPAN%3E%3F%3F%3C%2FP%3E%3CP%3Eplease%20take%20the%20below%20example%2C%20I%20attached%20the%20file%20as%20well.%3C%2FP%3E%3CP%3Ethank%20you%20very%20very%20much.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20576px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117186i5411B635DD9977C0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-06-04%20154123.jpg%22%20title%3D%22Annotation%202019-06-04%20154123.jpg%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-669381%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20change%20cell's%20background%20color%20by%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-669381%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354709%22%20target%3D%22_blank%22%3E%40vividwhp%3C%2FA%3E%26nbsp%3B%2C%20select%20your%20range%20without%20first%20column%20in%20it%2C%20create%20new%20conditional%20formatting%20rule%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20719px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117187i0947E55BD9E0781B%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%0A%3CP%3Ein%20formula%20it%20shall%20be%20relative%20references%20starting%20from%20top%20left%20cell%20of%20the%20range%20(N5).%20Apply%20desired%20format%2C%20Ok%2C%20here%20confirm%20or%20change%20selection%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20492px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117188iB5E8EE19BB8AA139%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%0A%3CP%3EThat's%20all.%20You%20have%20only%20one%20rule%20to%20color%20your%20entire%20range%2C%20result%20is%3C%2FP%3E%0A%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%2F117189i8473EACC3331ECBE%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%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-669514%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20change%20cell's%20background%20color%20by%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-669514%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%3Bthank%20you%20so%20much%20you%20are%20the%20best!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-672802%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20automatically%20change%20cell's%20background%20color%20by%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-672802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F354709%22%20target%3D%22_blank%22%3E%40vividwhp%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
vividwhp
New Contributor

Hello,

I need some little help here, I'd like to write a formula to automatically change a cell's background color when the next cell's content (text or value) is different from the previous cell's content, for example please see the attached picture, 

Annotation 2019-06-04 145306.jpg

As long as the next cell's content is different from the previous cell's content, the next cell's background color will be changed automatically. Does anyone know how to write a formula for this function?? I prefer writing formula instead of creating a rule because by this I can copy and paste this formula to apply to tons of other cells. Thank you sooooo much.

5 Replies
Highlighted

@vividwhp , formulas don't return cell properties as color, only values. That with VBA programming.

 

At the same time the rule with simple formula could be applied to all your tons of cells. If they are in one range that will be only one rule for entire range, otherwise approximately as many as many ranges you have. 

Highlighted

@Sergei Baklan , thank you so much for the reply, i see..........

so.......could you please quickly show me how to create the rule for this function for a entire range??

please take the below example, I attached the file as well.

thank you very very much.

Annotation 2019-06-04 154123.jpg

 

Highlighted
Solution

@vividwhp , select your range without first column in it, create new conditional formatting rule as

image.png

in formula it shall be relative references starting from top left cell of the range (N5). Apply desired format, Ok, here confirm or change selection

image.png

That's all. You have only one rule to color your entire range, result is

image.png

 

Highlighted

@Sergei Baklan thank you so much you are the best!!!

Highlighted
Related Conversations
SharePoint 2013 LIst Filters (by latest date)
dmphil in SharePoint on
0 Replies
Allow Teams application start minimized
jbarberan in Microsoft Teams on
3 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
Renaming Visio tabs is affecting styling
hkenny in Visio on
0 Replies