Target.Address & Target.Address.Row
I am trying to use Target.Address
and target.Address.row
, but I keep getting Invlaid qualifier. I would appreciate it if anyone can help you.
code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo Error1
If Target.Column = 10 Then
If Target.Address.Value = "Y" Then
Target.Address.Row.Interior.Pattern.Color = 255
End If
End If
Y
Letscontinue:
Application.EnableEvents = True
Exit Sub
Error1:
MsgBox Err.Description
Resume Letscontinue:
End Sub
+3
source to share
4 answers
I think one of them is what you are trying?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sPass As String
'~~. This is to prevent the code from crashing when a paste happens in more
'~~> than 1 cell. Also in Pre Excel versions, replace .CountLarge with .Count
If Target.Cells.CountLarge > 1 Then Exit Sub
sPass = "PASSWORD" '<~~ Your password
Application.EnableEvents = False
On Error GoTo Error1
If Not Intersect(Target, Columns(10)) Is Nothing And _
UCase(Trim(Target.Value)) = "Y" Then
ActiveSheet.Unprotect sPass
Target.EntireRow.Interior.Color = 255
Target.EntireRow.Locked = True
ActiveSheet.Protect sPass
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Error1:
MsgBox Err.Description
Resume Letscontinue
End Sub
+3
source to share
Small correction to duDE's answer using property EntireRow
....
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 Then
If Target.Value = "Y" Then
Target.EntireRow.Interior.Color = 255
End If
End If
End Sub
Use the Interior property Color
, not the propertyPatternColor
+3
source to share