force recalculation of function
 

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
--
Valeria

force recalculation of function
 

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 wrote:

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
--
Valeria

--

Dave Peterson

force recalculation of function
 

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


"Dave Peterson" wrote:

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 wrote:

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
--
Valeria

--

Dave Peterson
.