macro starting when color is changed?
 

Ok so I posted up last week about a macro I have that is a function (see
below). The only problem is that it doesn't automatically update the
information when I change the color of the cell. (The function counts the
number of cells with a specific color and displays the total in the cell).
The function activates when you actually double click in the cell so if you
just change the color it won't count the cells after updating. I am looking
for some suggestions or advice on how to go about this. I am looking for a
macro of some sort that would maybe start upon any change in the background
color of a cell then rerun the functions in the cells with this color
function so people don't have to worry about actually double clicking in the
cell do get it to count again. I am not all that familiar with macros so any
suggestion will do. Here is the function I am running currently.

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

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

I don't know if I should add another module and call the function when a
color is changed or what. Any help with this subject is greatly
appreciated!!! Thanks a lot in advance.

Cheers,
Steve

macro starting when color is changed?
 

Check out the section on Constraints

http://www.xldynamic.com/source/xld.ColourCounter.html
--
HTH...

Jim Thomlinson


"SteveK" wrote:

Ok so I posted up last week about a macro I have that is a function (see
below). The only problem is that it doesn't automatically update the
information when I change the color of the cell. (The function counts the
number of cells with a specific color and displays the total in the cell).
The function activates when you actually double click in the cell so if you
just change the color it won't count the cells after updating. I am looking
for some suggestions or advice on how to go about this. I am looking for a
macro of some sort that would maybe start upon any change in the background
color of a cell then rerun the functions in the cells with this color
function so people don't have to worry about actually double clicking in the
cell do get it to count again. I am not all that familiar with macros so any
suggestion will do. Here is the function I am running currently.

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

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

I don't know if I should add another module and call the function when a
color is changed or what. Any help with this subject is greatly
appreciated!!! Thanks a lot in advance.

Cheers,
Steve