Apr 18 2020 02:07 AM
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
Apr 18 2020 02:56 AM
Nope, that's not a formula, that's VBA programming. Formulas in Excel work with cell values, not with cell properties as colour.
Apr 18 2020 04:32 AM
@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.
Apr 18 2020 04:36 AM
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.
Apr 18 2020 05:44 AM
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
Apr 18 2020 05:47 AM
Apr 18 2020 06:15 AM
@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.