Excel Formula in Formatting Colour

%3CLINGO-SUB%20id%3D%22lingo-sub-1317769%22%20slang%3D%22en-US%22%3EExcel%20Formula%20in%20Formatting%20Colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1317769%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%2C%3C%2FP%3E%3CP%3EI%20would%20appreciate%20if%20Someone%20could%20help%20on%20the%20attached%20worksheet.%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20%2C%20whereby%20if%20I%20Highlight%20a%20Cell%20on%20a%20particular%20column%20%2C%20the%20Cell%20Amount%20will%20be%20automatically%20deducted%20at%20Cell%20G30.%3C%2FP%3E%3CP%3EI%20am%20really%20lost%20but%20am%20completely%20sure%20there%20is%20a%20formula%20for%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanking%20You%20in%20Advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERafiq%20M%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1317769%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1317813%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20in%20Formatting%20Colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1317813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F625643%22%20target%3D%22_blank%22%3E%40Rafiq_M%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENope%2C%20that's%20not%20a%20formula%2C%20that's%20VBA%20programming.%20Formulas%20in%20Excel%20work%20with%20cell%20values%2C%20not%20with%20cell%20properties%20as%20colour.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1317897%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20in%20Formatting%20Colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1317897%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F625643%22%20target%3D%22_blank%22%3E%40Rafiq_M%3C%2FA%3E%26nbsp%3BIf%20you%20are%20open%20to%20a%20VBA%20solution%2C%20you%20could%20place%20the%20following%20code%20in%20a%20module%20to%20create%20a%20User%20Defined%20Function%20that%20reads%20out%20the%20colour%20of%20a%20cell.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EFunction%20FindColor(x)%0A%0A%20%20%20%20Application.Volatile%0A%0A%20%20%20%20FindColor%20%3D%20x.Interior.Color%0A%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EAdd%20a%20helper%20column%20to%20your%20sheet%20and%20find%20the%20colour%20for%20cells%20in%20column%20G.%20Then%2C%20your%20total%20formula%20may%20look%20like%20this%20(amend%20the%20ranges%20where%20needed)%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%3CBANK%20balance%3D%22%22%3E%20%2BSUMIF(H6%3AH23%2C16777215%2CG6%3AG23)%3C%2FBANK%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EA%20cell%20without%20fill%20colour%20has%20the%20colour%20code%2016777215.%20With%20the%20help%20of%20SUMIF%20you%20can%20thus%20sum%20cells%20without%20a%20fill%20colour.%20The%20helper%20column%20can%20easily%20be%20hidden%2Funhidden%20with%20the%20group-button.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20file%20has%20the%20formulae%20in%20it.%20Note%20that%20the%20calculation%20updates%20as%20soon%20as%20you%20enter%20something%20that%20trigger%20the%20sheet%20to%20recalculate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1317906%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20in%20Formatting%20Colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1317906%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENOTE%3A%20The%20above%20solution%20will%20work%2C%20but%20then%20you%20might%20as%20well%20skip%20the%20finding%20of%20colours%20and%20just%20add%20the%20helper%20column%20to%20indicate%20the%20status%20of%20the%20amount%20in%20G.%20Then%20do%20the%20SUMIF%20on%20the%20status.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1317993%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20in%20Formatting%20Colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1317993%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%20for%20your%20fast%20response.%3C%2FP%3E%3CP%3EI%20tried%20it%20.%20when%20I%20Highlighted%20Cell%20G7%20to%20Colour%203243501%20%2C%20it%20doesnt%20Adjust%20automatically.%3C%2FP%3E%3CP%3EIt%20adjusts%20only%20when%20I%20copied%20the%20H6%20to%20H7%3C%2FP%3E%3CP%3EIs%20that%20the%20way%20it%20is%20suppose%20to%20be%20or%20is%20there%20any%20shorter%20way%3C%2FP%3E%3CP%3EPlease%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3CP%3E%26nbsp%3B%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-1317997%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20in%20Formatting%20Colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1317997%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F625643%22%20target%3D%22_blank%22%3E%40Rafiq_M%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20the%20modified%20sheet%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-1318023%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20in%20Formatting%20Colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F625643%22%20target%3D%22_blank%22%3E%40Rafiq_M%3C%2FA%3E%26nbsp%3BUnfortunately%2C%20that%20how%20it%20is.%20You%20have%20to%20trigger%20a%20calculation.%20Just%20changing%20the%20fill%20colour%20doesn't%20do%20that.%20But%20have%20yu%20considered%20my%20other%20comment.%20Just%20forget%20about%20filling%20cells%20with%20a%20colour.%20It's%20probably%20quicker%20to%20just%20put%20an%20%22x%22%20behind%20an%20amount%20if%20it's%20not%20yet%20in%20the%20bank%20balance%20and%20SUMIF%20on%20these.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi ,

I would appreciate if Someone could help on the attached worksheet.

I need a formula , whereby if I Highlight a Cell on a particular column , the Cell Amount will be automatically deducted at Cell G30.

I am really lost but am completely sure there is a formula for it.

 

Thanking You in Advance

 

Rafiq M

 

6 Replies
Highlighted

@Rafiq_M 

Nope, that's not a formula, that's VBA programming. Formulas in Excel work with cell values, not with cell properties as colour.

Highlighted

@Rafiq_M If you are open to a VBA solution, you could place the following code in a module to create a User Defined Function that reads out the colour of a cell.

Function FindColor(x)

    Application.Volatile

    FindColor = x.Interior.Color

End Function

Add a helper column to your sheet and find the colour for cells in column G. Then, your total formula may look like this (amend the ranges where needed):

=<bank balance> +SUMIF(H6:H23,16777215,G6:G23)

A cell without fill colour has the colour code 16777215. With the help of SUMIF you can thus sum cells without a fill colour. The helper column can easily be hidden/unhidden with the group-button.

 

The attached file has the formulae in it. Note that the calculation updates as soon as you enter something that trigger the sheet to recalculate.

 

Highlighted

@Riny_van_Eekelen 

NOTE: The above solution will work, but then you might as well skip the finding of colours and just add the helper column to indicate the status of the amount in G. Then do the SUMIF on the status.

Highlighted

@Riny_van_Eekelen 

Thank You for your fast response.

I tried it . when I Highlighted Cell G7 to Colour 3243501 , it doesnt Adjust automatically.

It adjusts only when I copied the H6 to H7

Is that the way it is suppose to be or is there any shorter way

Please help

 

Thank You

 

 

 

Highlighted

@Rafiq_M 

I attached the modified sheet

 

 

Highlighted

@Rafiq_M Unfortunately, that how it is. You have to trigger a calculation. Just changing the fill colour doesn't do that. But have yu considered my other comment. Just forget about filling cells with a colour. It's probably quicker to just put an "x" behind an amount if it's not yet in the bank balance and SUMIF on these.