Update Cell function after Value Change

Question

I have a VBA that counts the number of COLORED CELLS. The VBA module is asigned to a cell. But, the function only runs when the i click on the cell function and press ENTER. Changing a cell value does not run the function automatically. AUTOMATIC UPDATE of formulas are enabled in options too.

Here's my VBA:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

and Im calling this module using the worksheet command : =ColorFunction(J70,$B$3:$BV$66)

Any help?? Thanx

score 0 · Answer 1 · answered Feb 28 '17 at 07:05

you could use a little workaround

in the relevant sheet code pane place the following code

Option Explicit

Dim myColor As Long '<--| variable to store the "preceeding" color

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim coloredRng As Range

    If myColor > 0 Then '<--| if a "colored" cell had been selected in the "preceeding" selection, then store its color
        Me.Calculate '<--| trigger calculation and, by its effects, all functions with 'Application.Volatile'
        myColor = 0 '<--| reset "preceeding" color to zero. it'll be set to a vaild color if user has currently selected a "colored" cell
    End If

    Set coloredRng = Range("J70:J73") '<--| set the range whose color change must "trigger" 'ColorFunction()' (change "J70:J73" to your actual "colored" cells addresses)
    If Not Intersect(Target, coloredRng) Is Nothing Then myColor = Target.Interior.Color '<--| if current selection belongs to the "colored" range then store its color to trigger 'ColorFunction()' cells as soon as the user leaves the current selection
End Sub

this will actually trigger all ColorFunction() function after the users has:

changed the color of a valid cell (one of those you list in coloredRng)
left the changed colored cell

so you'll experience a little delay but it'll work

@KADAragorn, did you get through it? – user3598756 Mar 03 '17 at 07:12 — user3598756, Mar 03 '17 at 07:12

score -1 · Answer 2 · edited May 23 '17 at 12:25

-1

I think you need to set Application.Volatile.

Further details here:

Refresh Excel VBA Function Results

Hope it helps.

edited May 23 '17 at 12:25

Community

1
1

answered Feb 28 '17 at 05:43

Mihai Ovidiu Drăgoi

1,307
1
10
16

Thank you for the reply... Application.Valatile only works when a VALUE a changed. :( Not when the background Cell color changes.. Any fix for that? I dont even mind if there is a button to update the cells.. – KAD Aragorn Feb 28 '17 at 06:06
Did you try these: Ctrl+Alt+ F9 Recalculates all worksheets in all open workbooks (Full recalculation) Shift + Ctrl+Alt+ F9 Rebuilds the dependency tree and does a full recalculation – Mihai Ovidiu Drăgoi Feb 28 '17 at 06:12

Update Cell function after Value Change

2 Answers2