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
LVL 6
Uptime Legal SystemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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)?
byundtMechanical EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff DarlingDeveloper AnalystCommented:
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 SystemsAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.