Target.Address & Target.Address.Row

Question

I'm trying to use Target.Address and target.Address.row however I keep get Invlaid qualifier. I would be grateful if anyone can offer some help please.

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

The key is to listen when VBA is trying to tell you what's wrong. `.Address` gets highlighted when it says "Invalid qualifier". What do you think `Address` does? Have you [looked it up](http://msdn.microsoft.com/en-us/library/office/ff837625%28v=office.14%29.aspx)? What do you expect `.Address.Value` to mean? — Jean-François Corbett, Sep 09 '14 at 07:31
the lonely `Y` you have in the above code - does it serve a purpose? — whytheq, Sep 09 '14 at 07:45
I see that you have unaccepted my answer. Well, I don't have a problem with that :D However I would like to know that after almost a month what problem did you find with my code? — Siddharth Rout, Nov 04 '14 at 16:26

score 3 · Answer 1 · answered Sep 09 '14 at 07:39

3

Small amendment to duDE's answer by using the EntireRow property ....

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

Please use the Interior's Color property rather than PatternColor property

answered Sep 09 '14 at 07:39

whytheq

34,466
65
172
267

Thanks for your input. The action (row color) it is still telling me "Object required". Not sure what I need to ammend here. – user1624926 Sep 09 '14 at 07:42
strange as I have tested - I will amend with full signature – whytheq Sep 09 '14 at 07:43
why are you bothering with the labels `Letscontinue:` etc ? – whytheq Sep 09 '14 at 07:44
Lonely "Y" is my error. It needs to be drop kicked. "Letscontinue" is for another section - I removed out that section to make my issue clear. Really appreciate your input (and that of others). I keep getting the "Object requried" on the color line. – user1624926 Sep 09 '14 at 07:48
@whytheq `why are you bothering with the labels Letscontinue: etc ?` Perhaps [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) will answer your question – Siddharth Rout Sep 09 '14 at 07:51
@SiddharthRout thanks - in your post you mentioned `if writing data to cell` - in this post that is not the case – whytheq Sep 09 '14 at 07:58
Yes, you are right but it is a good practice to include that. For example It is good practice to include `If Target.Cells.CountLarge > 1 Then Exit Sub` Else the code will break if you try to paste on large cells. :) BTW + 1 For `.EntireRow.Interior.Color` – Siddharth Rout Sep 09 '14 at 08:02

Siddharth Rout · Accepted Answer · 2014-09-09T08:05:09.310

3

I think one of these 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

edited Sep 09 '14 at 08:05

answered Sep 09 '14 at 07:48

Siddharth Rout

147,039
17
206
250

Morning Siddharth - thank you for your comments last night and links to your code - really appreciated. "Target.Value" in detecting the "Y" no problem. However I'm getting an "Object required" for the "Target.EntireRow.Interior.Pattern.Color = 255" – user1624926 Sep 09 '14 at 07:53
Ok what color do you want in the row interior? – Siddharth Rout Sep 09 '14 at 07:53
I have amended the post above. Now Try it. You may have to refresh the page – Siddharth Rout Sep 09 '14 at 07:54
Any color at all. I tested multiple codes but no joy with this "object required". I was using this as a test to see clearly if it was working. When I have it tested I'm just going to change the color and add ".lock" – user1624926 Sep 09 '14 at 07:55
To lock, you have to protect the sheet as well. I have updated the code above. Now test it – Siddharth Rout Sep 09 '14 at 07:59
1

Thank you for all the support this morning. – user1624926 Sep 09 '14 at 08:00

score 0 · Answer 3 · answered Sep 27 '19 at 06:54

0

Target has not property Target.Address.Row, but has Target.Row. It can be this error reason.

answered Sep 27 '19 at 06:54

Sharunas Bielskis

1,033
1
16
25

Target.Address & Target.Address.Row

3 Answers3