Link to home
Start Free TrialLog in
Avatar of Uptime Legal Systems
Uptime Legal SystemsFlag for United States of America

asked on

Excel recalculation

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 Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountColor = CountColor + 1
    End If
Next datax
End Function

Open in new window


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.ColorIndex

End Sub

Open in new window


But I still can't get Excel to re-calculate when the color is changed.  Any advice on this?

Thanks
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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)?
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Uptime Legal Systems

ASKER

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.