1

When an Excel sheet contains cells with subtle color gradations (e.g., 20%, 40% accents of the same color), color-based counts that only use the Interior Color Index do not give accurate results--not in my work, at least. So I had the bright idea of basing the comparison and subsequent counts on RGB color.

I modified a function I found on the web with one of the functions given here (called the helper function below) so that the comparison and count would depend on finer-grained RGB values (output as text).

 Function CountRGB(CellColor As Range, SumRange As Range)
 '
 ' SumByColor Function
 ' VBA and Macros for Microsoft Excel by Bill Jelen "Mr.Excel"
 ' Page 84
 '
 ' Modified 02/04/2007 by Stanley D. Grom, Jr.

    Dim oneCell As Range
    Dim myCell As Range
    Dim iCol As Integer
    Dim myTotal As Integer
    Dim N As Double
    Dim a As Variant
    Dim b As Variant

    iCol = CellColor.Interior.ColorIndex
    a = Str(iCol Mod 256) & ", " & Str(Int(iCol / 256) Mod 256) & ", " & Str(Int(iCol / 256 / 256) Mod 256)

    For Each myCell In SumRange
        N = myCell.Interior.ColorIndex
        b = Str(N Mod 256) & ", " & Str(Int(N / 256) Mod 256) & ", " & Str(Int(N / 256 / 256) Mod 256)
        If b = a Then
            myTotal = myTotal + 1
        End If
    Next myCell
    CountRGB = myTotal
End Function

enter image description here
enter image description here

The helper function is outputting distinct text values for these shades, and a comparison of these text outputs in the spreadsheet yields the correct result (but apparently not in the VBA code):

enter image description here enter image description here

I tried to improve the code by using the EXACT Worksheet function in the code, but in my version of Excel (Excel for Mac, version 14.4.2), this function is not available in VBA.

Some of the discussions I read in stackoverflow suggest utilizing the criteria used for color coding in conditional statements to do the counts (e.g., here)--this is not a workaround I am interested in.

Is there some way of modifying this code to make it work?

Thanks for your consideration.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
user2738815
  • 1,196
  • 3
  • 12
  • 19
  • @chris neilsen Thank you, I made the change in the Worksheet VBA. Unfortunately, still getting the same incorrect counts. – user2738815 Oct 03 '17 at 08:07
  • Assume you mean that the **helper** function is `FillColorRGB` from the other question ? Anyway, your code works for me on Windows-based Excel. – Robin Mackenzie Oct 03 '17 at 10:48
  • @Robin Mackenzie Yes, that is the function. I am glad it is useful to other users :) Did you try it with "close" shades like the ones I use? – user2738815 Oct 03 '17 at 18:05
  • I just used the grades of green from the standard dropdown. The greens looked pretty close to your greens but the RGB values weren't an exact match to yours. In any case, your issue is about string comparisons, not the actual colours - and your code still works despite issues with using Integer/ Double and String/ Variants. – Robin Mackenzie Oct 03 '17 at 23:20
  • @Robin Mackenzie Thanks for checking. As you might have noticed, the count is correct for the darkest shade, which is more "distant" from the other two than they are to each other (20% Accent, 40% Accent and Accent3, which is darker than the 60% Accent). But you are right, the problem is most likely with string comparisons in my version of Excel. – user2738815 Oct 04 '17 at 04:16

0 Answers0