I'm using an excel UDF that counts all the cells in a range that are a certain color:
Function CountColor(range_data As Range, criteria As Range) As Long Application.Volatile (True) Dim datax As Range Dim xcolor As Longxcolor = criteria.Interior.ColorIndexFor Each datax In range_data If datax.Interior.ColorIndex = xcolor Then CountColor = CountColor + 1 End IfNext dataxEnd Function
This works great. However, when the color is updated, it doesn't re-calculate the formulas. I have added as a module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static LastRange As Range 'The last range selected Static LastColorIndex As Integer 'The color index of the last range selected If LastRange.Cells(1).Interior.ColorIndex <> LastColorIndex Then Worksheets("Sheet1").Calculate .Calculate End If Set LastRange = Target LastColorIndex = Target.Interior.ColorIndexEnd Sub
But I still can't get Excel to re-calculate when the color is changed. Any advice on this?
Thanks
Microsoft ExcelVisual Basic Classic
Last Comment
Uptime Legal Systems
8/22/2022 - Mon
Martin Liss
What do you mean when you say "I have added as a module:"? Do you mean you put that code in a module like Module1 or is it in your sheet (where it belongs)?
Thanks all for replies, byundt yours did the trick for me with the existing workbook- Jeff I appreciate your time as well, this was another good approach.