IF Statement using VBA

[mykal66]

Example.xlsxHi

I have a workbook where as soon as the user enters a value column A (Range A3:A100) the value in Column B is Automatically Set to the value Cell D2 on a separate ADMIN tab and the value in Column E is Set to Cell E2 on thre same ADMIN Tab which works no problem. I was wondering if it is possible to do the same thing using VBA instead?

I have some code that looks at the range and displays messages boxes but can't find anything to replace the message boxes but cannot figure out how to replaces the messages boxes with value from the other tab.


Thanks

Mykal

Private Sub Worksheet_Change(ByVal Target As Range)Dim cel As Range
    For Each cel In Range("A3:A100")
        If cel.Value >= 1 / 1 / 2020 Then
            MsgBox "Date"                                    THIS SHOULD BE THE VALUE OF D2 on the admin tab
        Else
            MsgBox "Blank  "                                 THIS SHOULD BE THE VALUE OF E2 on the admin tab
        End If
    Next cel
End Sub

[JKwan]

        If cel.Value >= 1 / 1 / 2020 Then
            MsgBox "Date" & Worksheets("admin").Range("D2")                                 ' THIS SHOULD BE THE VALUE OF D2 on the admin tab
        Else
            MsgBox "Blank  " & Worksheets("admin").Range("E2")                                'THIS SHOULD BE THE VALUE OF E2 on the admin tab
        End If

[mykal66]

Hi JKwan and thanks for coming back to me but i think i have explained things wrong. I don't want message boxes at all but rather columns B & E to updated with the values from the admin tab when a date is entered into the range in column A. I have uploaded and example workbook to my original post to show what i am trying to achieve

[JKwan]

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    For Each cel In Range("A3:A100")
        If cel.Value >= 1 / 1 / 2020 Then
            cel.Offset(0, 1) = Worksheets("admin").Range("D1")                               ' THIS SHOULD BE THE VALUE OF D2 on the admin tab
        Else
            cel.Offset(0, 2) = Worksheets("admin").Range("B1")                                'THIS SHOULD BE THE VALUE OF E2 on the admin tab
        End If
    Next cel
    Application.EnableEvents = True
End Sub

[mykal66]

Thank you, slight tweak but you got me there - very much appreciate your help.

[SamT]

Private Sub Worksheet_Change(ByVal Target As Range)
       If Not Intersect(Target, Range("A3:A100")) Is Nothing then SetBE Target
End Sub

Private Sub SetBE(Target As Range)
Dim Cel As Range
     Set Cel = Target 'Stop using var Target ASAP
     If Cel.Count > 1 Then Exit Sub 'Change was not made by a User

Appplication.EnableEvents = False 'Don't let the Changes to B and E trigger another Change Event

   With Sheets("ADMIN")  
        ' "Cel" is still on the "Targeted" Sheet
        ' An unqualified "Space Dot Range" is on the "With'ed" Sheet
        ' "Cells" with no dot refers to the "Targeted" Sheet

         If Cel >= 1/1/2020 Then
            Cel.Offset(0, 1) = .Range("D2")
         Else
            'Alternate style of addressing
            Cells(Cel.Row, "E") = .Range("E2")
         End If

   End With

Appplication.EnableEvents = True
End Sub