Using Worksheet change on calculated cells

[mandalocascio]

Hello,

I have been looking for a couple of hours now and have found a lot of information that is a bit beyond my understanding of excel.

1. I have a cells on a worksheet (at the moment 2, likely to be more later) that calculate a weighted average
2. they have conditional formatting once they are above a certain value (2.5) ie red for very high, orange for high etc - so i dont want tio use conditional formatting or data validation
3. I want to display a message box that gives a warning - particular to the calculated cell i.e. the 2 messages are different
4. i think i need to set each one as a target cell underneath a Private Sub Worksheet_Change(ByVal Target As Range)?
5. I found a thread that suggests that i need to include the calculation in the VBA ???

6. this is what i have - I'm missing something because recalculating either H37 or H57 give test 2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Range1 As Range, Range2 As Range
Set Range1 = Me.Range("$H$37")
Set Range2 = Me.Range("$H$57")

If Not Intersect(Range1, Target) Is Nothing Then
'Rule for Range1
If Target.Value > 0.25 Then 'condition
MsgBox "test 1"
End If

ElseIf Intersect(Range2, Target) Is Nothing Then
If Target.Value > 0.25 Then 'condition
MsgBox "test 2"
End If
End If
End Sub

 

[My Aswer Is This]

Assuming these values are being changed manually
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H37")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value > 0.25 Then MsgBox "H7 is greater then 0.25"
End If
If Not Intersect(Target, Range("H57")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value > 0.25 Then MsgBox "H57 is greater then 0.25"
End If
End Sub

 

[L. Howard]

Try this.

Howard

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("H3,H5")) Is Nothing _
   Or Target.Count > 1 Then Exit Sub
    
If Target.Address(0, 0) = "H3" And Target.Value > 0.25 Then
  MsgBox Target.Address & " Greater than .25"
End If

If Target.Address(0, 0) = "H5" And Target.Value > 0.25 Then
  MsgBox Target.Address & " Greater than .25"
End If

End Sub

Sorry, I used the wrong cells, but you can change them.

 

[My Aswer Is This]

If your values are changing as a result of a calculation change.
Try this:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
    If Range("H37").Value > 0.25 Then MsgBox "H37 is greater then 0.25"
    If Range("H57").Value > 0.25 Then MsgBox "H57 is greater then 0.25"
Application.EnableEvents = True
 End Sub

 

[mandalocascio]

This works thanks ! and Happy New Year !

Of course now that its working it is clear that there is a missing step.....

I have added if the cells are less than (not very cleverly but it works)

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("H37").Value > 2.5 Then MsgBox "H37 is greater than 0.25"
If Range("H37").Value < 2.5 Then MsgBox "H37 is less than 0.25"
If Range("H57").Value > 2.5 Then MsgBox "H57 is greater than 0.25"
If Range("H57").Value < 2.5 Then MsgBox "H57 is less than 0.25"

Application.EnableEvents = True
End Sub



1. if the line If Range("H57").Value > 2.5 Then MsgBox "H57 is greater than 0.25" is true then the cells that contribute to calculating H37 need to be cleared and re - asseseed - they are steps in a flow chart ( not sure if that helps visually?
2. H 37 is calculated as a weighted average from drop down lists ( data validation) and values taken from a Vlook up
3. Should i use a clear data something behind a button on the data sheet ??

thanks again, Amanda

 

[mandalocascio]

No sorry necessary thanks for answering !
the Private Sub Worksheet_Calculate() approach works i dont understand why Private Sub Worksheet_Change(ByVal Target As Range) doesnt work on calculated cells.....

 

[My Aswer Is This]

Private Sub Worksheet_Change(ByVal Target As Range)

Only works when you manually change a sheet value.

 

[mandalocascio]

I'm back !

It actually doesnt work.

it gives me both message boxes when i clear the cells to start again. maybe i need to check that there is a value there first ?

arghghghg i want to bang my head !

 

[My Aswer Is This]

Assuming your using my script. I see you have made changes. Your telling the script if the value is greater than do this and if less than do this and using the same value. So I would think the value must be greater than or less than no matter what value is entered.

You never mentioned in your original post wanting less than.

 

[My Aswer Is This]

If the cell is empty it is considered less than 0.25
You never said to ignore cell value if empty