Range selection with generated row location

[zoog25]

Hello all,

I'm having a debugging issue on a set of code.

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Count > 1 Then Exit Sub


'Initiate Work
Dim lrow As Long
Dim NOCws As Worksheet
Dim TPws As Worksheet


Set NOCws = ThisWorkbook.Worksheets("NOC")
Set TPws = ThisWorkbook.Worksheets("Tract Parcels")


'Disable other sheet events
Application.EnableEvents = False
'Find last row in NOC Log
lrow = NOCws.Cells(Rows.Count, 5).End(xlUp).Row


'NOC Auto Entry After Map goes to Council
If Not Intersect(Target, Range("E:F")) Is Nothing Then
    If WorksheetFunction.CountA(Cells(Target.Row, "E").Resize(, 2)) = 2 Then
        MsgBox ("NOC Log Updating")
        With NOCws.Range("F4:F" & lrow)
            Set NOCTP = .Find(Cells(Target.Row, "G"), LookIn:=xlValues)
            Do
                If Not NOCTP Is Nothing Then
                    firstaddress = NOCTP.Address
                    If Cells(Target.Row, "H").Value = NOCws.Cells(NOCTP.Row, "G").Value Then
                        If Cells(Target.Row, "D").Value = NOCws.Cells(NOCTP.Row, "E").Value Then
                            MsgBox "NOC entry already made.", vbInformation, "ENTRY MADE"
                            GoTo DoneFinding
                        End If
                    Set NOCTP = .FindNext(NOCTP)
                    End If
                Else
                    lrow = lrow + 1
                    If MsgBox("Is this Private Site Project?", vbQuestion + vbYesNo, "Public or Private") = vbYes Then
                        NOCws.Cells(lrow, "A") = "PR"
                    Else
                        NOCws.Cells(lrow, "A") = "PUB"
                        NOCws.Range("W" & lrow & ":Y" & lrow).Select
                        With Selection.Interior
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorLight1
                            .TintAndShade = 0.499984740745262
                            .PatternTintAndShade = 0
                        End With
                    End If
                    NOCws.Cells(lrow, "B") = Cells(Target.Row, "A").Value
                    NOCws.Cells(lrow, "C") = Cells(Target.Row, "B").Value
                    NOCws.Cells(lrow, "D") = Cells(Target.Row, "E").Value
                    NOCws.Cells(lrow, "E") = Cells(Target.Row, "D").Value
                    NOCws.Cells(lrow, "F") = Cells(Target.Row, "G").Value
                    If Cells(Target.Row, "H").Value = "" Then
                        NOCws.Range("G" & lrow).Select
                        With Selection.Interior
                        .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorLight1
                            .TintAndShade = 0.499984740745262
                            .PatternTintAndShade = 0
                        End With
                    Else
                        NOCws.Cells(lrow, "G") = Cells(Target.Row, "H").Value
                    End If
                    NOCws.Cells(lrow, "I") = Cells(Target.Row, "I").Value
                    MsgBox "Agreement has been added to NOC Log.", vbInformation, "ENTRY MADE"
                End If
            Loop While Not NOCTP Is Nothing
DoneFinding:
        End With
    End If
End If


Application.EnableEvents = True


End Sub

The section of in red that is giving me issues

NOCws.Range("W" & lrow & ":Y" & lrow).Select

Now i have tried to do it several ways, but i get debug issues. What i'm trying to do is on sheet NOCws, the empty row indicated by lrow would select cells from "W" lrow to "Y" lrow. For whatever reason, the way i am doing this, is not being accepted. Can anyone please let me know what i'm doing wrong or is there a better way to use Range when you have a set columns but variable row.

 

[John_w]

The .Select requires NOCws to be the active sheet. Try merging that line with the With line below it like this:

                        With [COLOR=#ff0000]NOCws.Range("W" & lrow & ":Y" & lrow)[/COLOR].Interior
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorLight1
                            .TintAndShade = 0.499984740745262
                            .PatternTintAndShade = 0
                        End With

Similarly change the other .Select.

 

[zoog25]

Thank you John_W. Making that changed worked. I tried it with several example and it works perfect.