Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Thursday, 05 January 2023
  1 Replies
  3.3K Visits
0
Votes
Undo
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?
1 year ago
·
#3338
0
Votes
Undo
Hi there,

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 Function


Please give it a try.

Amanda
  • Page :
  • 1
There are no replies made for this post yet.