Having difficulty with If statement

[Dkeller12]

I am trying to build a macro that will look at three different cell values and highlight them if all three conditions do not exist. Here is what I need: In the data below, I need a statement that basically says: if column A contains a 5 digit value that ends in "Cr" AND column C begins with "BX" AND column D begins with "0" (zero), do nothing, otherwise highlight the row. Additionally, I need another statment to say: if column A contains a 3 digit value AND column C is equal to "22000" AND column D begins with a "6", do nothing, otherwise highlight row. I have been struggling with this cannot seem to grasp the begins with and contains portion. Any help would be greatly appreciated.

A	B	C	D
508	38	22000	60125
509	38	22000	60125
50E	NM3	22000	60125
511Cr	00MB1	BXV05	08697
515Cr	00M3P	BXC05	08697
51CCr	00MP2	BXB05	18697

<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>

 

[Andrew Poulsom]

For begins with you can use the Left function and for contains 3 digits you can use Len. But why do you need a macro when a Conditional Formatting formula will do the job?

 

[NeonRedSharpie]

Sub checkstuff()

Dim highlight As Boolean


For x = 2 To 7 Step 1
    If Len(Cells(x, 1)) = 5 And Right(Cells(x, 1), 2) = "Cr" Then
        If Left(Cells(x, 3), 2) = "BX" Then
            If Left(Cells(x, 4), 1) = "0" Then
                highlight = False
                GoTo exitTests
            Else
                highlight = True
                GoTo testTwo
            End If
        Else
            highlight = True
            GoTo testTwo
    End If
    Else
        highlight = True
        GoTo testTwo
    End If
    
    
testTwo:


    If Len(Cells(x, 1)) = 3 Then
        If Cells(x, 3) = "22000" Then
            If Left(Cells(x, 4), 1) = "6" Then
                highlight = False
                GoTo exitTests
            Else
                highlight = True
                GoTo exitTests
            End If
        End
            highlight = True
            GoTo exitTests
        End If
        highlight = True
        GoTo exitTests
    End If
                


exitTests:


Select Case highlight
    Case True
        Cells(x, 1).EntireRow.Font.Color = vbRed
    Case False
End Select
    


Next


End Sub

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG29Aug13
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] True
        [COLOR="Navy"]Case[/COLOR] Len(Dn) = 5 And Right(Dn, 2) = "Cr" And Left(Dn.Offset(, 2), 2) = "BX" And Left(Dn.Offset(, 3), 1) = "0"
        [COLOR="Navy"]Case[/COLOR] Len(Dn) = 3 And Dn.Offset(, 2) = "22000" And Left(Dn.Offset(, 3), 1) = "6"
        [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Else[/COLOR]
        Dn.Resize(, 4).Font.ColorIndex = 3
[COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Darren Bartrup]

I think these two conditional formats should work:

=NOT(AND(LEN($A2)=5,RIGHT($A2,2)="Cr",LEFT($C2,2)="BX",VALUE(LEFT($D2,1))=0))
=NOT(AND(LEN($A2)=3,$C2=22000,VALUE(LEFT($D2,1))=6))

Rows 1, 2, 3 and 6 are TRUE on the first condition,
rows 4 and 5 are TRUE on the second condition.

 

[Dkeller12]

For begins with you can use the Left function and for contains 3 digits you can use Len. But why do you need a macro when a Conditional Formatting formula will do the job?

I guess I was just thinking an if statement was the best way to go.

 

[Dkeller12]

Thank you to all that replied. I was able to utilize MickG's coding and all worked perfectly. Cheers and have a great weekend.