EXCEL VBA - Subtracting By Cell Color - Basic Subtraction Formula

Question

Here is my VBA

Function CellColour(Irow As Integer, Icol As Integer) As Long
CellColour = Cells(Irow, Icol).Interior.ColorIndex
End Function

The color of the grey cells I am using is, -4142 I found this by =CellColour(5,11)

I currently have Two rows that contain monthly Sales data, Once the month ends I manually color the row gray, ""-4142"

I have a section for totals D6 which is a sum of a few cells D6 = Sum(D9:D12)

What I want to accomplish is inside the D6 cell... subtract this gray number.

Cell D6 Formula: Sum(D9:D12)-If(Cellcolour *IN ROWS F11:Q12* = *GRAY "-4142)

End result SUM D9:D12 MINUS WHATEVER NUMBERS ARE GRAY FROM ROWS F11:Q12

I think my problem lies within my inability to create the proper formula.

I feel like just doing conditional formatting might be easier?

Any help would be awesome.

Thanks!

I've played around with formula and cell colours before here. Perhaps that will help you... — Glitch_Doctor
Using a cell color to encode information is not a great approach: you'd be much better off with a "Status" column (which could then drive conditional formatting to add the color, but also can be more-easily used in other formulas) — Tim Williams
most aggregate functions like sum ignore non-numeric values, so you can just prepend ' in front of the numbers to convert them to text and hence excluding them from the sum — Slai

Tim Williams Tim Williams · Accepted Answer · 2018-02-19T23:11:19

Using a cell color to encode information is not a great approach: you'd be much better off with a "Status" column (which could then drive conditional formatting to add the color, but also can be more-easily used in other formulas)

That aside, your function has a problem:

Function CellColour(Irow As Integer, Icol As Integer) As Long
    CellColour = Cells(Irow, Icol).Interior.ColorIndex
End Function

...here the Cells() will always apply to the ActiveSheet, which may or may not be the one you expect/want.

This might explain why your "grey" cells give -4142, which is actually the code for "no fill" (xlNone)

This is more explicit in referencing the correct sheet, because you're using a Range parameter in place of numeric arguments:

Function CellColour(r As Range) As Long
    CellColour = r.Cells(1).Interior.ColorIndex
End Function

EDIT: This might be more what you need:

Function SumByColour(r As Range, indx As Long) As Long
    Dim c As Range, t As Double, v
    Application.Volatile
    For Each c In r.Cells
        v = c.Value
        If IsNumeric(v) And c.Interior.ColorIndex = indx Then
            t = t + v
        End If
    Next c
    SumByColour = t
End Function

Then you can do something like:

 =SUM(D9:D12)-SumByColour(F11:Q12, 15)

EXCEL VBA - Subtracting By Cell Color - Basic Subtraction Formula

1 Answers