Excel recalculation

Uptime Legal Systems
Uptime Legal Systems used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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)?
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I tested your code with the Worksheet_SelectionChange in the Sheet1 code pane (as Martin Liss was suggesting). Rightclick the Sheet1 tab and choose View Code--that's where the Worksheet_SelectionChange sub must go.

The code as posted failed on statement 7, which I then deleted. The code then failed on statement 5 because LastRange didn't yet exist. I fixed this latter problem with:
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 Not LastRange Is Nothing Then
    If LastRange.Cells(1).Interior.ColorIndex <> LastColorIndex Then
       Worksheets("Sheet1").Calculate
    End If
   End If

   Set LastRange = Target
   LastColorIndex = Target.Interior.ColorIndex

End Sub

Open in new window

CountColorsQ28935597.xlsm
Jeff DarlingDeveloper Analyst
Commented:
I found some code that watches the color change.  This may be the approach you are looking for.

http://www.mrexcel.com/forum/excel-questions/574405-capturing-cell-color-change-visual-basic-applications.html

I wrote a simple sheet that uses that code, turned off autocalc, and then set some colors to set the value to some cells, either 1, 2 or 3, then have a total of these values.

sample attached.
ee_2016032501_colortest.xlsx.xlsm
Uptime Legal Systems

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial