Suggestion for a macro?

[SteveK]

Hey everyone,
Ok this is going to be a detailed description so if it is a little
confusing so bare with me. My macro experience is nothing more than beginner
also so any sort of help will be much appreciated. What I have is a building
entry pass list of about 250 people and 5 different categories that people
are defined with.

What I need is a macro that will count the number of cells, that have a
specific background color and display the results in a single cell below the
range. I.E. One of the categories is "Need Deleted" and has a red background
color. There are 5 different colors and 6 different columns. Each column of
names has a spacer column between it (This is where some of my problems
arise). The way the spread sheet is setup, the results are in a range of
there own with the specific amount for each color displays for the
corresponding column. As of right now i have a COLORFUNCTION equation in
there that counts them but it doesn't automatically update the result when
you change the color. (EX. =ColorFunction(D57,E5:E54,FALSE)).

A guy I work with found this macro but I can't seem to get it working
because, well I just don't have that much experience with macros (It is a
general macro and nothing is defined specifically yet.):

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As
Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com

'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''

lCol = rColor.Interior.ColorIndex

If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If

ColorFunction = vResult
End Function


I took one look at that and got confused about all the ranges and such. I
am wondering if anyone has any ideas on if i can use this macro or if there
is a better route to the solution, or if I am just using the colorfunction
wrong and it is on my end that it is not automatically updating when i change
the color. Thanks a lot in advance for any help received.

Cheers,
Steve

 

[Dave F]

Well the first question is: did you follow the links at ozgrid.com explaining
the colorindex and how that figures into the macro? Some info on colorindex
here: http://www.cpearson.com/excel/colors.htm

Basically, the color of the cells you want to count has a number associated
with it; that number (the color index of the cell shading) needs to be
specified.

Dave

 

[SteveK]

Dave,
I got all the integers relating to the specific color, but I don't know
how to incorporate this into the macro. I tried keeping it simple and only
doing one column and counting only one color, but I don't know how to start
the macro to see if it works (can't just click run and select the macro).
I'm guessing that it will run automatically once I figure out how to do get
it to work. I tried one of the macros from the site below and this is what I
have come up with so far. Check it out:

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function returns the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True 'not exactly sure what this is for
InRange = Range(Cells(h, 5), Cells(h, 55)) 'Range to look for the cells
WhatColorIndex = 3
Rng = Cells(h, 67) 'I think this is the resulting cell?

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
'Thinking about deleting the If function because it doesnt apply
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function