Forum Discussion
Excel Formula in Formatting Colour
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
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 FunctionAdd 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.
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.
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
Nope, that's not a formula, that's VBA programming. Formulas in Excel work with cell values, not with cell properties as colour.
