Forum Discussion

Rafiq_M's avatar
Rafiq_M
Copper Contributor
Apr 18, 2020

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 ...
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Apr 18, 2020

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.

 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 18, 2020

    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.

    • Rafiq_M's avatar
      Rafiq_M
      Copper Contributor
      Apr 18, 2020

      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

       

       

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Apr 18, 2020

        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.

Resources