Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Conditional Formatting and Data Validation
=AND(LEN(A1)=7,AND(LEFT(A1)>=”A”,LEFT(A1)<=”Z”),
AND(MID(A1,2,1)>=”A”,MID(A1,2,1)<=”Z”),AND(MID(A1,3,1)>=”A”,
MID(A1,3,1)<=”Z”),AND(MID(A1,4,1)>=”A”,MID(A1,4,1)<=”Z”),
MID(A1,5,1)=”-”,AND(VALUE(MID(A1,6,2))>=0,
VALUE(MID(A1,6,2))<=99))
For a simpler approach, write a custom VBA worksheet function. The VBA Like operator makes
this sort of comparison relatively easy. The following VBA function procedure returns TRUE if its
argument does not correspond to the part number rules outlined previously:
Function INVALIDPART(n) As Boolean
If n Like “[A-Z][A-Z][A-Z][A-Z]-##” Then
INVALIDPART = False
Else
INVALIDPART = True
End If
End Function
After defining this function in a VBA module, you can enter the following conditional formatting
formula to apply special formatting if cell A1 contains an invalid part number:
=INVALIDPART(A1)
Figure 19-24 shows a range that uses the custom INVALIDPART function in a conditional
formatting formula. Cells that contain invalid part numbers have a colored background.
 
Search JabSto ::




Custom Search