force recalculation of function

[Valeria]

Daer experts,
I have a UDF which I would like to force to recalculate for every input.
I have a sheet with a Worksheet_Change macro to make all user inputs on a
range red in color; and the UDF which is put in a nearby column should
immediately recalculate to show "Filter for Changes".
What happens today with my code is that the recalculation has a 1 input
delay: that is to say, I make my input, nothing happens, I enter a second
input and here the formula recalculates on the 1st input
Does this have something to do with the worksheet_Change event?
Here is my code:

Function FilterForRed(c As Range) As String
Application.Volatile True
If Worksheets("Sheet1").Cells(c.Row, 19).Font.ColorIndex = 3 Or
Worksheets("Sheet1").Cells(c.Row, 21).Font.ColorIndex = 3 _
Or Worksheets("Sheet1").Cells(c.Row, 23).Font.ColorIndex = 3 Then
FilterForRed = "Filter For Changes"
Else
FilterForRed = ""
End If
End Function


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 18 And Target.Column < 24 Then
Target.Font.ColorIndex = 3
End If
End Sub

Thank you very much in advance for your help!
Kind regards

 

[John Bundy]

This seems to work fine for me. Is your function in a module and your
Worksheet Change event on the worksheet? If so, can you give an example of
what you are putting in the cells and in which order?

 

[Valeria]

Hi John,
yes, the function is in a module and my Worksheet Change event on the
worksheet.

I am putting numebrs on the cells and the order is random - it is a volume
forecast.
I have 3 columns (19, 21 and 23) corresponding to 3 months and the sales
people put in their forecast on the row correpsonding to their customers
(therefore it's a random input). The colums are already pre-filled with the
historical plan, this is why I want it to become red when something is
changed, so that we have a trace for the change.

Thanks!
Kind regards

 

[Valeria]

By doing it over and over again i have the impression that the first thing
the code does is to calculate the function (so when you type in the number it
is black in the first step), so the function returns "". Then the worksheet
code is activated so that the number becomes red, but it so no longer
recognized by the function. At the second time I type something in, then the
function recognizes the first number which has ebcome red but not second one
etc.

Is there a way to put an action priority to fucntion & worksheet code? First
the code, then the fucntion?
Thanks!

 

[Dave Peterson]

Just a curiosity question...

Is there a reason you don't just add code to the worksheet_change event to put
that string in whatever cell you want it in?

====

When you (or your macro) change the format of a cell, there is nothing that
notifies excel that it should recalculate.

Even the application.volatile instruction won't help. That just tells excel
that the next time it's doing a recalculation to include the cells with this
function.

An alternative (I wouldn't do this!) would be to force a recalc in your _change
event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 18 And Target.Column < 24 Then
Target.Font.ColorIndex = 3

application.recalculate '<-- added

End If
End Sub

I'd drop the UDF completely and use something like:

But depending on how long the recalc takes, this could drive you batty!

=============
I'm kind of confused over why you're not changing the function for all the cells
in S:W and I don't know what cell gets that warning message. And I don't know
how you clear the colors and that warning message. (that's a lot!)

But this may be a way you want to look at to see if it's worth pursuing. (I
used column Z as the indicator column.)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myIntersect As Range
Dim myCell As Range
Dim RngToCheckForChanges As Range
Dim RngToCheckForResetting As Range

Set RngToCheckForChanges = Me.Range("S:W")
Set RngToCheckForResetting = Me.Range("Z:Z")

Set myIntersect = Intersect(Target, RngToCheckForChanges)
If Not (myIntersect Is Nothing) Then
'in columns S:W
For Each myCell In myIntersect.Cells
myCell.Font.ColorIndex = 3
'I don't know why you're only checking columns S, U, W,
'but you are!
Select Case myCell.Column
Case Is = 19, 21, 23
'stop the macro change from firing the _change event
Application.EnableEvents = False
Me.Cells(myCell.Row, "Z").Value = "Filter For Changes"
Application.EnableEvents = True
End Select
Next myCell
Else
Set myIntersect = Intersect(Target, RngToCheckForResetting)
If Not (myIntersect Is Nothing) Then
'in column Z
For Each myCell In myIntersect.Cells
If myCell.Value = "" Then
'it's been cleared, so reset the colors
Intersect(myCell.EntireRow, RngToCheckForChanges) _
.Font.ColorIndex = xlAutomatic
End If
Next myCell
End If
End If

End Sub

 

[Valeria]

the only reason being that I had not thought about it - very nice and simple,
many thanks!