CODEWITHSUNDEEP
excelvbamsgbox
Peter Mogford
Peter Mogford

Reputation: 496

MSG Macro on Sheet change if offset value = TRUE

I would like to display a msgbox if the formula fuelled cell in column I:I changes to TRUE after the cell in column D:D is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Range

myRange = Range("D:D")

If Intersect(myRange, Target) Then

    If Target.Offset(0, 3).Value = True Then MsgBox "Date Range and Holiday Type Mismatch"

End If

End Sub

This is an exaxmple of the table. Basically i will update column D:D with the holiday type. In column I:I the cell will change to TRUE if the date range is not acceptable. If the cell in column I:I changes to TRUE i want the msg box to display.

enter image description here

Upvotes: 0

Views: 60

Answers (1)

BigBen
BigBen

Reputation: 50008

A good starting attempt, but several issues, including the need for Set when working with Range objects, and an offset that seems... off.

Here's one approach:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRng As Range
    Set myRng = Intersect(Target, Me.Columns("D:D"))

    If myRng Is Nothing Then Exit Sub

    Dim myCell As Range
    For Each myCell In myRng
        If Me.Cells(myCell.Row, "I").Value = True Then
            MsgBox "Date Range and Holiday Type Mismatch"
        End If
    Next
End Sub

Upvotes: 1

Related Questions