DinnerPlates


Hi all,

I have created a macro (thanks once again to the help of Andy) which looks like this:



Re: Obtain formula value in VBA

Andy Pope


Hi,

I assume you problem is that the changing of cells outside the range M,N and O is not being processed even though it impacts on the formula in column M. If so try this revision to the code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lngRow As Long
Dim blnProcess As Boolean

On Error GoTo ErrDependents

If Not Intersect(Range("MSurprise"), Target.Dependents) Is Nothing Then
blnProcess = True
End If

ErrDependents:
If Not Intersect(Range("MSurprise"), Target) Is Nothing Then
blnProcess = True
End If

If blnProcess Then
Application.EnableEvents = False
lngRow = Target.Row
If Range("M" & lngRow).Value < Range("O" & lngRow).Value Then
Range("C" & lngRow) = "Display Date"
Range("B" & lngRow) = "Display Amnt"
MsgBox "Please order this brochure ASAP", vbInformation, "MVC Brochure Stores"
Else
Range("C" & lngRow) = ""
Range("B" & lngRow) = ""
End If
Application.EnableEvents = True
End If

End Sub







Re: Obtain formula value in VBA

DinnerPlates

Would just like to express my thanks to Andy, you've made my life a whole lot easier.
Thanks.





Re: Obtain formula value in VBA

Andy Pope

You're welcome. Glad I was able to help.