Counting Colors in Excel Row

[rwmill9716]

I use a function (shown in the accompanying image) that identifies the color in Cell (Q3) and then counts the number of times that color appears in each row (column Q). If I color the Cells manually, which I have done here, the function works fine, but if I use a conditional formatting rule to place the color, my function doesn't count it. I would save a lot of work if I could use conditional formatting rules. Is there a change to the function that would count the number of gold cells placed there by a conditional formatting rule?

 

[Rick Rothstein]

You cannot check Conditional Formatted cell colors directly, but you can by using a "helper" function. Replace the code you posted with these two functions. I kept the main function's name the same as you named it so your formulas (if you have them on the sheet) should now work.

Function CountCcolor(Range_Data As Range, Criteria As Range) As Long
  Dim DataX As Range, XColor As Long
  Application.Volatile
  XColor = Criteria.Interior.ColorIndex
  For Each DataX In Range_Data
    If Evaluate("MyHelperFunction(" & DataX.Address & ")") = XColor Then
      CountCcolor = CountCcolor + 1
    End If
  Next
End Function

Private Function MyHelperFunction(ByVal R As Range) As Double
 MyHelperFunction = R.DisplayFormat.Interior.ColorIndex
End Function

 

[rwmill9716]

Thanks, Rick, that works great.