Error In Trying To Get Cell Address

[Ark68]

I have a range of cells (H6:H45) that the user can change, validated by cell validation.
When they change one of those cells, I wish to process some code based on that entry.

I first have to find which cell was changed, and if that cell that changed is within range H6:H45, execute the change.

I assume the code goes into the worksheet change event code. (See my code highlighted in blue below as an attempt to do this.)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim fmr_month As String
    Dim ta As Range
    Dim rng_ta as range
    'Stop
    If Not mbevents Then Exit Sub
    With ws_dsched
     
      'YEAR
        If Target.Address = "$D$2" Then
       
        'ERROR CHECK
            'if year entered is text
            If IsNumeric(.Range("D2")) = False Then
                MsgBox "Please enter a valid year. [>2019]", vbCritical, "INVALID YEAR"
                mbevents = False
                .Range("D2") = td_yr
                .Unprotect
                n_date = DateSerial(td_yr, td_month, td_day)
                .Protect
                mbevents = True
                Exit Sub
            End If
            'if year is post season
            pyear = Format(DateAdd("yyyy", -1, td_date), "yyyy")
            If .Range("D2") < CInt(pyear) Then
                MsgBox "Please enter a valid year. [>" & pyear & "]", vbCritical, "INVALID YEAR"
                mbevents = False
                .Range("D2") = td_yr
                .Unprotect
                n_date = DateSerial(td_yr, td_month, td_day)
                .Protect
                mbevents = True
                Exit Sub
            End If
            'if year exceeds 4 digits
            If .Range("d2") > 9999 Then
                MsgBox "Please enter a valid year. [<9999]", vbCritical, "INVALID YEAR"
                mbevents = False
                .Range("d2") = td_yr
                .Unprotect
                n_date = DateSerial(td_yr, td_month, td_day)
                .Protect
                mbevents = True
                Exit Sub
            End If
           
            td_yr = .Range("D2")
            n_date = DateSerial(td_yr, td_month, td_day)
            mbevents = False
            .Unprotect
            .Range("G2") = Format(n_date, "ddd")
            .Protect
            mbevents = True
           
            'leap year
            Leap_Year
           
            Month_Validation

        End If
       
        If Target.Address = "$F$2" Then
            'Stop
            'MsgBox "Day change"
            td_day = .Range("F2")
            n_date = DateSerial(td_yr, td_month, td_day)
            mbevents = False
            .Unprotect
            .Range("G2") = UCase(Format(n_date, "ddd"))
            .Protect
            mbevents = True
        End If
       
        If Target.Address = "$E$2" Then
            'MsgBox "Month change"
            fmr_month = MonthName(td_month)
            txt_month = .Range("E2")
            td_month = Month(DateValue("01-" & txt_month & "-1900"))
            n_date = DateSerial(td_yr, td_month, td_day)
           
            'day conflict
            mbevents = False
            'Stop
            If td_month = 2 Then 'in a leap year February has 29 days
                If usr_lp_yr = True Then
                    If td_day > 29 Then 'error
                        MsgBox "Please adjust the day before adjusting the month." & Chr(10) & txt_month & " only has 29 days.", vbCritical, "DATE CONFLICT"
                        .Range("E2") = UCase(fmr_month)
                        .Range("F2") = td_day
                        mbevents = True
                        Exit Sub
                    End If
                Else 'not a leap year
                    MsgBox "Please adjust the day before adjusting the month." & Chr(10) & txt_month & " only has 28 days.", vbCritical, "DATE CONFLICT"
                    .Range("E2") = UCase(fmr_month)
                    .Range("F2") = tm_day
                    mbevents = True
                    Exit Sub
                End If
            End If
            If td_month = 4 Or tm_month = 6 Or tm_month = 9 Or tm_month = 11 Then
                If td_day > 30 Then 'error
                        MsgBox "Please adjust the day before adjusting the month." & Chr(10) & txt_month & " only has 30 days.", vbCritical, "DATE CONFLICT"
                        .Range("E2") = UCase(fmr_month)
                        .Range("F2") = tm_day
                        mbevents = True
                        Exit Sub
                End If
            End If
            mbevents = True
           
            mbevents = False
            .Unprotect
            .Range("G2") = Format(n_date, "ddd")
            .Protect
            mbevents = True
           
            'with month change comes day range change
           Month_Validation

            mbevents = True
        End If
       
        mbevents = False
        .Unprotect
        'new tomorrow1()
        tomorrow = n_date + 1
        tm_day = Day(tomorrow)
        tm_month = Month(tomorrow)
        tm_yr = Year(tomorrow)
        tm_date = DateSerial(tm_yr, tm_month, tm_day)

        'new yesterday1()
        yesterday = n_date - 1
        yd_day = Day(yesterday)
        yd_month = Month(yesterday)
        yd_yr = Year(yesterday)
        yd_date = DateSerial(yd_yr, yd_month, yd_day)
       
        .Range("J2") = Format(yd_date, "ddd mmm dd yyyy")
        .Range("M2") = Format(tm_date, "ddd mmm dd yyyy")
        mbevents = True
        .Protect
   
       
        Set ta = Target.Address(0, 0)
        Set rng_ta = .Range("H6:H45")
        MsgBox ta & " has been changed."
        If InRange(ta, .Range("H6:H45")) Then
            MsgBox ta & " is within range H6:H45"
        Else
            MsgBox ta & " out of range."
        End If

    End With
End Sub

I am using this function to determine whether the changed cell falls within the range.

Function InRange(Range1 As ta, rng_ta As Range) As Boolean
    ' returns True if ta is within rng_ta
    Dim InterSectRange As Range
    Set InterSectRange = Application.Intersect(ta, rng_ta)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
End Function

I am getting a "Type mismatch error" with the line highlighted in red.

 

[CSmith]

Remove .Address(0,0)

Just curious why not just use Target?

 

[CSmith]

How about?

  Set rng_ta = Range("H6:H45")

  MsgBox Target.Address & " has been changed."
  If Not Application.Intersect(rng_ta, Range(Target.Address)) Is Nothing Then
    MsgBox Target.Address & " is within range H6:H45"
  Else
    MsgBox Target.Address & " out of range."
  End If
  Set rng_ta = Nothing

 

[Ark68]

Thank you CSmith! This is a new concept for me, so I grabbed the first suggestion from Google to cover this concept. This has been a very educational solution for me.
Is there a way that I can set target.address as a variable? I will be using that address may times. eg. how would I get the value from that address?

 

[CSmith]

You could however Target.Address can be a range and not just a cell Range.Address

What do you think you need it in a variable for?

 

[Ark68]

As I think about it, I likely wouldn't. I think I will need the value at that address more than the address itself. How would I get the value in that address or assign a value to that address? Or get the row and column number of that address?

 

[Fluff]

@Ark68
You should know the rules regarding cross posting by now.
Please comply with them.
Thanks

 

[Ark68]

Yep. You're right. Slipped my mind Fluff.
Cross posted here ....

 

[RoryA]

FYI, since Target is already a Range object, there is almost never any point to using Range(Target.Address)

 

[Ark68]

Thanks Rory. As I continue moving forward in my code, I'm starting to see that.