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

 

[mandalocascio]

No I didn't mention that as i thought i could add it in to the original with an elseif but it didn't work. Apologies!

I did go back to your original (yes i am trying to use yours, thanks!) and manually cleared the sheet and the msgboxes still appear even though the isn't a calculated value in either H37 or H57 - can you help ?

I'll try again the MsgBoxes are messages that give directions in a flow chart.
Step 1.
calculate H37 using drop downs and VlookUps
if the calculated cell in H37 is less than 2.5 the message is to stop
if the calculated cell in H37 is greater than 2.5 - continue to step 2
Step 2 calculates the value of H57
if the calculated cell in H57 is less than 2.5 then stop
if the calculated step in H57 is greater than 2.5 then start again at step 1. - i have put a button on that clears the data validation drop downs

 

[My Aswer Is This]

I think what your asking for now is beyond my knowledge base. Maybe someone else at Mr. Excel will join in and be able to help you. Things get difficult when we want things to happen automatically when a cell value changes due to a formula change. And it even gets more complicated if you have several sheet event scripts in your sheet or other Macros that do certain things. Sheet change event scripts are more easy to deal with but that requires a manual cell change. I will continue to monitor this thread and see what I can learn.

 

[mandalocascio]

Ive given up, i think its not the best way to do his anyway as every change triggers a message box.... i guess this could be fixed but its way too complicated for me! Ive gone for a message in an accompanying cell, which i have conditionally formatted and only appears when the conditions are met.

Thanks for your help

 

[My Aswer Is This]

If you need more help doing things some other way tell me about it if you want and I may be able to help you.

Ive given up, i think its not the best way to do his anyway as every change triggers a message box.... i guess this could be fixed but its way too complicated for me! Ive gone for a message in an accompanying cell, which i have conditionally formatted and only appears when the conditions are met.

Thanks for your help