Ok........
Start Excel and open the file in question.
Open the VBE editor by hitting ALT F11
Open the Project Explorer by hitting CTRL R
In the Project Explorer pane look for your file. It will look like this:
VBAProject(your_filename.xls)
Select the VBAProject with your filename.
Right click and select Insert>Module
An empty window will open on the right side. This is called a module. To be
more specific, this is a GENERAL MODULE.
Paste the code from this link into the module:
http://xldynamic.com/source/xld.ColourCounter.html#code
Copy the entire contents of the "gray box". Some of the code is comments but
that won't affect anything.
Add this line of code where noted: Application.Volatile
----------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant
Application.Volatile '<-----add this line
If rng.Areas.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If
Ok, now exit the VBE and return to your spreadsheet, click the X to close
the VBE.
To use this code to count cells that are a certain color you must first know
what the color index number is for the color of interest. You can find these
index numbers by filling some cells and then using this formula. Fill cell
A1 with any color then enter this formula in B1:
=ColorIndex(A1)
Now, to see the "bug" I noted in my other reply change the fill color of
cell A1. You'll notice that the result of the formula did not change. Now
press F9. This triggers a calculation. You'll notice that the formula result
has now changed.
Ok, now, to count the "blue" cells in the range A1:A10:
=SUMPRODUCT(--(COLORINDEX(A1:A10)=5))
So, that's it!
Just remember that changing a cells color does not trigger a calculation.
You either have to trigger a manual calculation by hitting function key F9
or wait until an event triggered calculation occurs.
It's for the above reason that I never use this method. I'd rather build a
formula based on the logic of WHY the cells are colored although sometimes
it's not so obvious WHY cells are certains colors!
Biff
