Hello all,
I'm having problems with the following code:
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
Stop
'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"
[COLOR=#FF0000]NOCws.Range("W" & lrow & ":Y" & lrow).Select[/COLOR]
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 SubFor whatever reason, it isn't liking the red section of my code. I'm trying to select columns W through Y of row "lrow" and adding color to those cells. I'm not sure if i'm just not coding right but all examples i have seen point to this method. Please let me know if there is a different method or if i'm just missing something to make my code work. Thank you.