CODEWITHSUNDEEP
excelvba
JeffCh
JeffCh

Reputation: 95

How to SUMIF if cell is colored?

I found an user defined function online that may be useful for my case but I'm not entirely sure how it works. Please bear with me as I'm still a learner of VBA.

The function goes like this:

Function IsCellColored(CellRange As Range) As Variant
Application.Volatile

Dim Result() As Variant
ReDim Result(1 To 1, 1 To CellRange.Cells.Count)
Dim i As Integer

i = 1
For Each rCell In CellRange
   Result(1, i) = (rCell.Interior.ColorIndex <> xlNone)
   i = i + 1
Next rCell

IsCellColored = Result

End Function

Suppose I have a set of data in Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row) that I want the sum of the colored cells.

Conditions:

  1. If cell in Range is colored then get the sum of the colored cells in Range("D1")

  2. If cell in Range is not colored (ColorIndex = xlNone) then Range("D1").Value = 0

How do I edit/make the function work in my case? And, what is the formula I should use to get the sum?

Edit:

Public Sub sumBasedOnColor(ws As Worksheet)

Dim rg As Range

For Each ws In ActiveWorkbook.Worksheets
    With ws
        Set rg = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
    End With
    
    Dim v As Single
    Dim c As Range
    For Each c In rg
        If c.Interior.ColorIndex <> xlNone Then
            v = v + c.Value
        End If
    Next
    
    ws.Range("D1") = v
Next ws

End Sub

Upvotes: 0

Views: 607

Answers (1)

Ike
Ike

Reputation: 13014

If you want to sum values from column D you can use this code:

Public Sub sumBasedOnColor(ws as Worksheet)

Dim rg As Range
With ws
    Set rg = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
End With

Dim v As Single
Dim c As Range
For Each c In rg
    If c.Interior.ColorIndex <> xlNone Then
        v = v + c.Value
    End If
Next

ws.Range("D1") = v
End Sub

The code looks into each cell of column B - if it is colored - it takes the value and adds it to v.

You can call the sub e.g. like this:

Sub runAll()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    sumBasedOnColor ws
Next
End Sub

v will always start by 0 when calling with a new worksheet.

Upvotes: 1

Related Questions