mandalocascio
New Member
- Joined
- Nov 3, 2011
- Messages
- 29
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
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