From Sub to Private Sub Worksheet_Change?

[CarolineGMartin90]

Hi there!

Again looking for help! I have written up some code as a Macro (apologies if I am using terminology incorrectly - code that only fires once it is "run" or a button is created and pressed), and it workd beatifully (it has been a long process!). I did it as a Macro as I wanted to be able to use the F8 key to step through it to find out where errors were etc. Now that I am happy with how it runs, I want it as a Worksheet_change private sub as I want it to be done automatically for the user. Naively, I presumed I just needed to change the heading from

 Sub Test ()

to

 Private Sub Worksheet_Change (ByVal as Target Range)

. It however is not working, and not even giving me Error Codes to work with.

A Vlookup function exists in Column L (Index 12), that references user input in column A in order to enter a code (e.g. T1.1001, T2.2001 etc) in column L. Based on that code populated in column L, I want column N to be populated with a shift pattern (e.g. "08:00-16:00" for "*.1*" code). I have it working with the code below as a Macro, and have solved my indefinite looping issue.

Any advice on how to change the below code to automatic private sub would be greatly appreciated!

Option Explicit

Sub Test()
Application.EnableEvents = False
Dim Rownum As Integer
Dim Keepsearching As Boolean

Keepsearching = True

Rownum = 1

Do Until Keepsearching = False

If Cells(Rownum, 12).Offset(0, 2) <> "" Then
Keepsearching = False
    Else
    If Cells(Rownum, 12).Text Like "*.1*" And Cells(Rownum, 12).Offset(0, 2) = "" Then
    Cells(Rownum, 12).Offset(0, 2) = "08:00 - 16:00"
       
        Else
        If Cells(Rownum, 12).Text Like "*.2*" And Cells(Rownum, 12).Offset(0, 2) = "" Then
        Cells(Rownum, 12).Offset(0, 2) = "10:00 - 18:00"
           
            Else
            If Cells(Rownum, 12).Text Like "*.3*" And Cells(Rownum, 12).Offset(0, 2) = "" Then
            Cells(Rownum, 12).Offset(0, 2) = "12:00 - 20:00"
               
                Else
                If Cells(Rownum, 12).Text Like "N*" And Cells(Rownum, 12).Offset(0, 2) = "" Then
                Cells(Rownum, 12).Offset(0, 2) = "Night Shift"
                   
                    Else
                    If Cells(Rownum, 12).Text = "#N/A" Then
                    Keepsearching = False
                    Exit Do
                   

End If
    End If
        End If
            End If
                End If
                    End If

Rownum = Rownum + 1
Keepsearching = True

Loop
Application.EnableEvents = True
End Sub

Really appreciate all your help!

Many thanks,
Caroline

 

[CarolineGMartin90]

Never mind me - I have solved it!

For anyone who has this issue (just incase anyone is like me!) you simply change the

 Sub Test ()

with

 Private Sub Worksheet_SelectionChange

My issue was trying to change it to Worksheet_Change...