# Having difficulty with If statement

#### Dkeller12

##### Board Regular
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>

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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?

Code:
``````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``````

Try:-
Code:
``````[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

I think these two conditional formats should work:
Code:
``````=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.

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.

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

Replies
1
Views
1K
Replies
5
Views
478
Replies
0
Views
154
Replies
3
Views
2K
Replies
1
Views
474

### Forum statistics

1,221,052
Messages
6,157,632
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back