Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Use VBA to Automatically Calculate When any Cell Value Changes

0

I am using the following code to count the background color and font color of a cells. When I change the background color and font color on cells, I can manually force recalculation by pressing CTRL+ALT+SHIFT+F9. How do I force Excel to auto-calculate when a call in a given range changes? I'm a novice with VBA. Any help is appreciated. 

It's been requested that I provide context for this question. Here is a brief explanation:

My mom has a tree farm with 1000 trees. I created a spreadsheet (one cell for each tree) to allow her to rate the health of the trees using a green, yellow, red rating system. Some of the trees were planted in 2020 and others in 2021. Black font color = 2020 and red font color = 2021. She asked if I could calculate stats based on these parameters. So, I found the code pasted here to count cells based on their background and font color and it works just fine. The problem is that I have to press CTRL+ALT+SHIFT+F9 to force the spreadsheet to recalculate. I was hoping to find a way to have it recalculate as she shades a cell or changes the font color. I've attached the spreadsheet as well. 

Function ColorInteriorFontCount(SearchRange As Range, colorRange As Range) As Long
    Dim cell As Range, a As Range, b As Range, n As Integer

    ' preload for Union method (will Union with itself in first For loop)
    Set b = SearchRange(1).MergeArea(1)

    For Each cell In SearchRange
        Set a = cell.MergeArea(1)
        Set b = Union(a, b)
    Next

    ' a becomes the preload for the next Union; n will be used to exclude
    ' it from the count if it's not the right color
    n = a.Interior.Color = colorRange.Interior.Color

    For Each cell In b
        If cell.Interior.Color = colorRange.Interior.Color And _
           cell.Font.Color = colorRange.Font.Color Then
            Set a = Union(cell, a)
        End If
    Next

    ColorInteriorFontCount = a.Count - 1 - n

End Function
excel
posted by: jmbarnes65 on Mon Feb 28, 2022 at 11:04 am
Answer
Discuss

Answers

0

Hi JM and welcome to the Forum

You can do that useing an event macro at the worksheet level (see Don's tutorial here: Run a Macro when a User Does Something in the Worksheet in Excel)

Initially I misunderstood your question but the added context now allows a simple answer...

By design, functions are non-volatile and recalculated when a paramater changes. If you use the Worksheet_SelectionChange event with this code behind the sheet in question, the sheet (and the function) will recalulate when another cell is selected (say after its fill or font colour is chnaged):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Calculate

End Sub
That's illustrated in the first file below which has cell G6 with the formula:
=ColorInteriorFontCount(A2:C11,G4)
and looks for cells which match the fill/font colurs in G4.

Now if you change the fill of a cell in A2:C11 (to no fill say) and click another cell , you'll see that the number in G6 changes to suit (without the need to force  recalculation manually).

I think that will fix your problem but have left my original answer below (in case it helps people with a similar problem (and who can't have automatic recaculation set e.g. for speed reasons in large complex workbooks).

In the second attached file below, I've used the Worksheet_Change event instead which happens when a cell is changed on the worksheet behind which the macro sits.

That second file has the Calculation mode set to "Manual" and the macro sits behind the worksheet with tab name "Limit". It reacts when a change occurs but the first bold line means it does nothing if that wasn't in the yellow-shaded cells.

As the file leaves me, the formula in D2 is A2*C2 (and the current result is wrong). If you change C2 to 7 say,  the macro will be triggered, the whole sheet is re-calculated (giving the right result in D2) and a message is given to the user:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A2:C11")) Is Nothing Then Exit Sub

Worksheets("Limit").Calculate
MsgBox "Sheet recalculated"

End Sub

If you want the code to run for any cell on the sheet, delete the first bold line. If you don't want the message, delete the second bold line.

Hope this helps.

posted by: John_Ru (rep: 6142) on Mon Feb 28, 2022 at 12:49 pm
Discuss

Discussion

John,
Thanks so much for the reply. I was able to get your suggestion to work for some cell changes but not for background color or font color changes. For instance, if I change the value of the cell from 6 to 8, then the sheet recalculates and I get the message "Sheet recalculated". However, when I changes the background color or font color, I don't think these changes are recognized as changes (certainly not value changes) and the sheet does not recalculate. Any ideas?
jmbarnes65 Feb 28, '22 at 1:59 pm
JM. I'm miles from my PC now but why would you want to recalculate a sheet when only the colours change?

Note that if you change a font color of a cell in range say, you can press Enter on the current value of the cell (in the formula bar) and that should trigger the macro. 
John_Ru (rep: 6142) Feb 28, '22 at 2:13 pm
Oh do you mean how do you call the same function when a value changes? Is the output of the function in one or more cells on your sheet? If so, do you need to call the function with the new colours?

Please explain (but by editting your original question in case someone else wants to reply) and if possible provide an Excel file with test data (but no personal data) - you can do that using the Add Files... button in your question.
John_Ru (rep: 6142) Feb 28, '22 at 2:51 pm
Sorry to be a bother. Perhaps there is a better way to do this. I provided more detail in the edited original quesiton. 
jmbarnes65 Feb 28, '22 at 3:33 pm
Thanks for the context JM- it really helped but as file is nearly always more help to be honest).

Please see my revised Answer (especially the new bits after the new second paragraph and the new first file)and don't forget to mark this Answer as Selected if it fixes things for you.
John_Ru (rep: 6142) Feb 28, '22 at 5:36 pm
JM. Did you see my message above? 
John_Ru (rep: 6142) Mar 1, '22 at 1:49 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login