Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim message As String
If Intersect(Target, Range("B3:B7")) Is Nothing Then Exit Sub
If Range("B3") < Range("B5") Then message = message & "- ROIC should be higher than WACC" & vbLf
If Range("B4") < Range("B7") Then message = message & "- Perpetual Growth should be higher than Terminal Growth" & vbLf
If Range("B5") > Range("B4") Then message = message & "- WACC should be lower than ROIC" & vbLf
If Range("B7") > Range("B4") Then message = message & "- Terminal Growth should be higher than Perpetual Growth"
MsgBox message
End Sub
Thanks Rollis, this is working fine.. but what should be done if the range contains formula. And there should be no Message Box, when all the conditions are matching.As per your example this could be a quick solution to be pasted in the sheet's module. Since it triggers by event Worksheet_Change at every change of a cell in range B3:B7 (manual changes) you will see a popup with the response. If this range contains formulas you will need a completely different approach.
Code:Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim message As String If Intersect(Target, Range("B3:B7")) Is Nothing Then Exit Sub If Range("B3") < Range("B5") Then message = message & "- ROIC should be higher than WACC" & vbLf If Range("B4") < Range("B7") Then message = message & "- Perpetual Growth should be higher than Terminal Growth" & vbLf If Range("B5") > Range("B4") Then message = message & "- WACC should be lower than ROIC" & vbLf If Range("B7") > Range("B4") Then message = message & "- Terminal Growth should be higher than Perpetual Growth" MsgBox message End Sub
MsgBox message
If message <> "" Then MsgBox message
Worksheet_Change
, this other event macro but as it is it will trigger at every calculation in the entire sheet.
Option Explicit
Private Sub Worksheet_Calculate()
Dim message As String
If Range("B3") < Range("B5") Then message = message & "- ROIC should be higher than WACC" & vbLf
If Range("B4") < Range("B7") Then message = message & "- Perpetual Growth should be higher than Terminal Growth" & vbLf
If Range("B5") > Range("B4") Then message = message & "- WACC should be lower than ROIC" & vbLf
If Range("B7") > Range("B4") Then message = message & "- Terminal Growth should be higher than Perpetual Growth"
If message <> "" Then MsgBox message
End Sub