troubleshooting Question

Excel recalculation

Avatar of Uptime Legal Systems
Uptime Legal SystemsFlag for United States of America asked on
Microsoft ExcelVisual Basic Classic
4 Comments2 Solutions137 ViewsLast Modified:
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

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

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

Thanks
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros