I have a template that the user will enter numeric data into cell ranges A1:C10. Numbers will usually range from 1-500. Some cells will also be blank if no data is given to record. Some of the values will be typed in red font color and some will be in black font color. The user may change the data randomly while they use it along with also changing the color of the font on the fly. I have a button for the user that can change the font color between red and black as this spreadsheet is also locked by the traditional method of changing font colors in the ribbon. Here is the code I am using in a module to sum by font color:
Then I have these in cells A15 and C15 respectively:
=sumRed(A1:C10)
=sumBlack(A1:C10)
The problem that seems to be occurring is that the calculation in these cells don't seem to be on the fly or "live" I should say. It is being triggered by some odd events. Like if a cell was set to red because the user had previously typed red font into that cell and now wants to clear it and then change it to black font, it won't add it in C15 for black. It will add it into cell A15 for red because it was originally red. It's as if the calculation is happening before the font changes color. Is there a way to make this method of summing by font color more accurate?
Thanks for the help.
VBA Code:
Public Function sumRed(r As Range)
Dim ce As Range
sumRed = 0
For Each ce In r
If ce.Font.ColorIndex = 3 Then sumRed = sumRed + ce.Value
Next ce
End Function
Public Function sumBlack(r As Range)
Dim ce As Range
sumBlack = 0
For Each ce In r
If ce.Font.ColorIndex = 1 Then sumBlack = sumBlack + ce.Value
Next ce
End Function
Then I have these in cells A15 and C15 respectively:
=sumRed(A1:C10)
=sumBlack(A1:C10)
The problem that seems to be occurring is that the calculation in these cells don't seem to be on the fly or "live" I should say. It is being triggered by some odd events. Like if a cell was set to red because the user had previously typed red font into that cell and now wants to clear it and then change it to black font, it won't add it in C15 for black. It will add it into cell A15 for red because it was originally red. It's as if the calculation is happening before the font changes color. Is there a way to make this method of summing by font color more accurate?
Thanks for the help.