Thanks in advance for any help on this that you can offer.
With the help of this site I have created the following function;
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
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
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
In each target cell that I want a sum of all the of the cells in that row that contain the color of the specified cell;
=colorfunction(AR4,H5:AP5,TRUE)
How can I get the target sell to update when any other cell in the row (H5:AP5) changes color?
With the help of this site I have created the following function;
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
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
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
In each target cell that I want a sum of all the of the cells in that row that contain the color of the specified cell;
=colorfunction(AR4,H5:AP5,TRUE)
How can I get the target sell to update when any other cell in the row (H5:AP5) changes color?
Hi there,
You can add the code: Application.Calculation = xlAutomatic to your function:
Please give it a try.
Amanda
You can add the code: Application.Calculation = xlAutomatic to your function:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
Application.Calculation = xlAutomatic
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 FunctionPlease give it a try.
Amanda
- Page :
- 1
There are no replies made for this post yet.