How to count colored cells by row in excel-CodePudding

I have a report in excel with three rows with conditional formatting to color map the data. I essentially want the fourth column to output a 1 if at least two cells within the row are green and a 0 if not. Example:any two cells in the row can be green

So I would like a formula to output a 1 in E4 but 0 for the rest of column E. Is this possible?

CodePudding user response：

There are two options that I can think of:

create a formula that aligns with the conditional formatting - this has already been suggested by previous contributors. However, this does not actually check cell colors.
use a vba script. This can assess cell colors and can be setup as event driven so it runs automatically upon a new worksheet entry.

CodePudding user response：

You have to create a VBA function like this:

Function ColorComparer(rColor1 As Range, rColor2 As Range, rColor3 As Range) As String

Dim vResult As String
Dim greenCounter As Integer

iCol1 = rColor1.Interior.Color
iCol2 = rColor2.Interior.Color
iCol3 = rColor3.Interior.Color
green = RGB(0, 255, 0)
greenCounter = 0


If iCol1 = green Then
    greenCounter = greenCounter   1
End If

If iCol2 = green Then
    greenCounter = greenCounter   1
End If

If iCol3 = green Then
    greenCounter = greenCounter   1
End If

If greenCounter >= 2 Then
    vResult = 1
Else
    vResult = 0
End If

ColorComparer = vResult

End Function

The green color has been set to RGB (0, 255, 0) but you can change to any color you want

Way to use:

Best regards.