CODEWITHSUNDEEP
vbaexcel
Robert
Robert

Reputation: 69

VBA count color code not working properly with some blank cells

I have the following count color code, which is working fine until the range contains blank cells, for which you have to go in the function line and press enter, then the change into different kind of blank cells it seems, as i spotted the errors and everytime i do the step, the vba code is working again. How can I either correct the vba code so I can step this manual enter process for some blank cells, or is there an code that does the manual process for a certain range automatically?

Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double 
    'Update 20140210 
     Application.Volatile 
     Dim rng As Range 
     Dim xTotal As Double 
     xTotal = 0 
     For Each rng In pRange1 
         If rng.Font.Color = pRange2.Font.Color Then 
              xTotal = xTotal + rng.Value 
         End If 
     Next 
     SumByColor = xTotal 
End Function

Upvotes: 2

Views: 447

Answers (1)

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

Your function work for me. I don't know what settings do you have, but try mine modification:

Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
    'Update 20140210
     Application.Volatile
     Dim rng As Range
     Dim rngSum As Range

     For Each rng In pRange1
         If rng.Font.Color = pRange2.Font.Color And IsNumeric(rng.Value) Then
            If rngSum Is Nothing Then
                Set rngSum = rng
            Else
                Set rngSum = Union(rngSum, rng)
            End If
         End If
     Next
     SumByColor = WorksheetFunction.Sum(rngSum)
End Function

There is issue however for both mine and your version. It won't recalculate if you change font color for any cell. You must click Calculate Now in Formulas menu.

Upvotes: 0

Related Questions