I have a countcolour (VB script) result, how do I obtain a countif result within that result

Question

I have used a =countcolour script in VB and I ended up with a result. Example, "=countcolour(a1:a10,b1)" b1 being lets say green and the result is 6 green cells. What I would like to know is if there was a cell value of "G" entered in at random across the "A1:A10", how can I then calculate the "G" within the countcolour result?

if you are working in excel, it is not VB.NET. The text on the tags provides usage guidance. Please read [ask] and take the [tour] — Ňɏssa Pøngjǣrdenlarp, Jun 12 '17 at 21:57

score 0 · Answer 1 · answered Jun 12 '17 at 23:21

Not absolutely sure I understand what you're after, but if it's the equivalent of a COUNTIFS worksheet formula, the following code will work:

Sub SumCountByConditionalFormat()
Dim refColor As Long
Dim rng As Range
Dim countRng As Range
Dim countCol As Long

Set countRng = Sheet1.Range("$A$1:$A$10")

    refColor = Sheet1.Range("$B$1").DisplayFormat.Interior.Color
    For Each rng In countRng
        If rng.DisplayFormat.Interior.Color = refColor And rng.Value = "g" Then
            countCol = countCol + 1
        End If
    Next
    MsgBox countCol

End Sub

I have a countcolour (VB script) result, how do I obtain a countif result within that result

1 Answers1