IF AND

Hi, i have put together some code where i am checking for text and the color of the cell

On the bolded line i am getting the error message

"Object doesn not support this property or method"

Can anyone see where i am making an error?

Thanks
Seamus

With ActiveSheet

R = .Range("C" & Rows.Count).End(xlUp).Row

Range("B2").Select
Selection = "=IF(COUNT(MATCH(F2,'Sophis Paris'!A:A,0),MATCH(F2,'Sophis US'!A:A,0),MATCH(F2,'Sophis HK'!A:A,0))>0,""match"",""no match"")"


 .Range("A2:B2").Copy .Range("A2:B" & R)
 
 End With

For Each cell In Range("A2:A10000")

Range("A2").Select

If cell.Offset(0, 1).Selection = "Match" And cell.Offset(0, 2).Interior.ColorIndex = 3 Then

cell.Offset(0, 2).Interior.ColorIndex = 4



End If

Next cell


End Sub
Seamus2626Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
StephenJRConnect With a Mentor Commented:
Seamus - maybe this? In the example I see none of the cells in B contain "match".
With ActiveSheet
    R = .Range("C" & .Rows.Count).End(xlUp).Row
    .Range("B2").Formula = "=IF(COUNT(MATCH(F2,'Sophis Paris'!A:A,0),MATCH(F2,'Sophis US'!A:A,0),MATCH(F2,'Sophis HK'!A:A,0))>0,""match"",""no match"")"
    .Range("A2:B2").Copy .Range("A2:B" & R)
    For Each Cell In .Range("A2:A10000")
        If LCase(Cell.Offset(0, 1).Value) = "match" And Cell.Offset(0, 2).Interior.ColorIndex = 3 Then
            Cell.Offset(0, 2).Interior.ColorIndex = 4
        End If
    Next Cell
End With

End Sub

Open in new window

0
 
Zack BarresseCEOCommented:
You don't need to have the ".Selection" afterwards, in fact it's a waste and syntactically incorrect.  Try this instead...

    With ActiveSheet
        R = .Range("C" & .Rows.Count).End(xlUp).Row
        .Range("B2").Formula = "=IF(COUNT(MATCH(F2,'Sophis Paris'!A:A,0),MATCH(F2,'Sophis US'!A:A,0),MATCH(F2,'Sophis HK'!A:A,0))>0,""match"",""no match"")"
        .Range("A2:B2").Copy .Range("A2:B" & R)
    End With
    For Each Cell In .Range("A2:A10000")
        If Cell.Offset(0, 1).Value = "Match" And Cell.Offset(0, 2).Interior.ColorIndex = 3 Then
            Cell.Offset(0, 2).Interior.ColorIndex = 4
        End If
    Next Cell

Open in new window


HTH

Regards,
Zack Barresse
0
 
StephenJRCommented:
Try this:
For Each cell In Range("A2:A10000")
    'Range("A2").Select
    If cell.Offset(0, 1) = "Match" And cell.Offset(0, 2).Interior.ColorIndex = 3 Then
        cell.Offset(0, 2).Interior.ColorIndex = 4
    End If
Next cell

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Seamus2626Author Commented:
Hey guys, even though your code seems correct. When i run it, nothing happens.

Attached is the file where i have added where the code should turn the index to green but doesnt

Thanks
Seamus
test.xls
0
 
Zack BarresseConnect With a Mentor CEOCommented:
As it seems you are trying to format conditionally based on a cell value*, why don't you just use conditional formatting?

* I also see it's based on the color of column C values.  I'm assuming this is another conditional format?  You would need to know that condition as well, since you have two checks.

In any case, you don't need code for this, but only Conditional Formatting.  If you would like help with that, please post what the relevant conditions are.

Regards,
Zack Barresse
0
 
dlmilleConnect With a Mentor Commented:
>>syntactically incorrect

Selection as a range object has the default property of .Value.  

Selection = "=formula" is syntactically correct.

@seamus, that code you posted was close, but was checking for different case of "Match".  The following converts the case of the find and match string to MATCH and it works properly now.

Sub test()

    With ActiveSheet
        For Each cell In .Range("A1:A10000")
            If UCase(cell.Offset(0, 1)) = "MATCH" And cell.Offset(0, 2).Interior.ColorIndex = 3 Then
                cell.Offset(0, 2).Interior.ColorIndex = 4
            End If
        Next cell

    End With
End Sub

Open in new window


PS - column B drives the code, why not code as this (more efficiently):
Sub test()

    With ActiveSheet
        For Each cell In .Range("B1", .Range("B" & .Rows.Count).End(xlUp))
            If UCase(cell.Value) = "MATCH" And cell.Offset(0, 1).Interior.ColorIndex = 3 Then
                cell.Offset(0, 1).Interior.ColorIndex = 4
            End If
        Next cell

    End With
End Sub

Open in new window

See attached.

Dave
test.xls
0
 
Seamus2626Author Commented:
Thanks all, all sorted

Cheers
Seamus
0
 
Zack BarresseCEOCommented:
Dave,

I see where you're going, but I gotta say, I still disagree.  When I say "syntactically incorrect", I don't always necessarily mean "validly structured" or "non-compilable" or "will error out".  What I mean is that it is a non-professional way of coding.  We are allowed to make many exceptions when it comes to VBA, they really dumbed it down, didn't they?  I.e. the Value property is the default for the Range object.  ;)  Perhaps I should have rephrased.  Not best practice?  How does that work?

Selection = "=formula" is syntactically correct.
Wrong, Dave.  Does it work?  Sure, it'll compile, it'll get processed, it'll get executed, and it won't even throw an error of any kind.  Does that somehow imply it is "right" and we should use that as best practice?  What about in 2 years when somebody else has to debug or maintain your code, would it help them to have well written, well commented code, where you use properties fully and explicitly?  I'm going to assume you've been there (I think most of us have), and you know very well the answer (psst, it's YES).

Good catch on the last row methodology, I think I just got lazy.  :)

@Seamus: thanks for letting me have a soap box in your thread.  :)

Regards,
Zack Barresse
0
 
dlmilleCommented:
Then saying it is not a good practice would be More appropriate than syntactically incorrect

I do the same each time I see select activate and unqualified range use


Dave
0
 
Zack BarresseCEOCommented:
Touche.  I'll choose my words better.  :)

Have a great day, sir.

Zack
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.