You'll need to use the Worksheet_Calculate event instead.

To detect a change in value, you'll need to save the value in a module
or static variable so you can compare the old value to the current value.

For instance:

In a regular code module:

Public vOldC38Value As Variant

In the ThisWorkbook module:

vOldC38Value = Sheets("Sheet1").Range("C38").Value

In the worksheet code module:

Private Sub Worksheet_Calculate()
With Range("C38")
If .Value < vOldC38Value Then
vOldC38Value = .Value
Macro2
End If
End With
End Sub


In article ,
enyaw wrote:

I have the following code that runs a macro when the cell value chages.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$38" Then
Call Macro2
End If
End Sub

The problem I have that cell C38 contains a formula and will not run the
macro because any change in value is not picked up. Can anyone help me?

Try following JE's suggestion once more.

And if you still have trouble, you may want to give a little more info.

enyaw wrote:

The debugger tells me that there is a compile error.
Range C38 is invalid outside procedure. in the ThisWorkbook module.
I would also like it to work for the range c38:c51.

"JE McGimpsey" wrote:

You'll need to use the Worksheet_Calculate event instead.

To detect a change in value, you'll need to save the value in a module
or static variable so you can compare the old value to the current value.

For instance:

In a regular code module:

Public vOldC38Value As Variant

In the ThisWorkbook module:

vOldC38Value = Sheets("Sheet1").Range("C38").Value

In the worksheet code module:

Private Sub Worksheet_Calculate()
With Range("C38")
If .Value < vOldC38Value Then
vOldC38Value = .Value
Macro2
End If
End With
End Sub


In article ,
enyaw wrote:

I have the following code that runs a macro when the cell value chages.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$38" Then
Call Macro2
End If
End Sub

The problem I have that cell C38 contains a formula and will not run the
macro because any change in value is not picked up. Can anyone help me?


--

Dave Peterson

Try:

First, name your range, say, "CheckRange"

Then a regular code module:

Public vOldValues As Variant

In the ThisWorkbook code module:

Private Sub Workbook_Open()
vOldValues = ThisWorkbook.Names("CheckRange").RefersToRange.Val ue
End Sub

In the worksheet code module that contains CheckRange:

Private Sub Worksheet_Calculate()
Dim i As Long
With Range("CheckRange")
For i = 1 to .Cells.Count
If .Cells(i).Value < vOldValues(i, 1) Then
vOldValues(i, 1) = .Cells(i).Value
Macro2
End If
Next i
End With
End Sub

Or, if you only want Macro2 to run once, no matter how many cells have
changed:


Private Sub Worksheet_Calculate()
Dim i As Long
With Range("CheckRange")
For i = 1 to .Cells.Count
If .Cells(i).Value < vOldValues(i, 1) Then
vOldValues = .Value
Macro2
End If
Next i
End With
End Sub


In article ,
enyaw wrote:

I would also like it to work for the range c38:c51.

Think this is what I am looking for. Problem is I have entries that copy to
another worksheet. Want if any entry in row is changed need code to go to
copied entry and make change there also. Useing event to copy data. This
valadation on change is problem. Working primilary with text in cells. trying
to have multiple ifs in event code
Thanks
old dog new tricks

"JE McGimpsey" wrote:

You'll need to use the Worksheet_Calculate event instead.

To detect a change in value, you'll need to save the value in a module
or static variable so you can compare the old value to the current value.

For instance:

In a regular code module:

Public vOldC38Value As Variant

In the ThisWorkbook module:

vOldC38Value = Sheets("Sheet1").Range("C38").Value

In the worksheet code module:

Private Sub Worksheet_Calculate()
With Range("C38")
If .Value < vOldC38Value Then
vOldC38Value = .Value
Macro2
End If
End With
End Sub


In article ,
enyaw wrote:

I have the following code that runs a macro when the cell value chages.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$38" Then
Call Macro2
End If
End Sub

The problem I have that cell C38 contains a formula and will not run the
macro because any change in value is not picked up. Can anyone help me?