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

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

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.

LVL 59

Martin LissOlder than dirtCommented: 2016-03-25

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)?

LVL 82

byundtMechanical EngineerCommented: 2016-03-25

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:

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

LVL 13

Jeff DarlingDeveloper AnalystCommented: 2016-03-25

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

LVL 6

Uptime Legal SystemsAuthor Commented: 2016-03-25

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.

Excel recalculation

Who is Participating?