MSG Macro on Sheet change if offset value = TRUE

Question

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.

Answer 1

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