Microsoft Office Tutorials and References
In Depth Information
The following conditional formatting formula applies formatting to cell A1 if it contains a date and the date
falls on a weekend:
=AND(CELLHASDATE(A1),OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1))
Identifying invalid data
You might have a situation in which the data entered must adhere to some very specific rules, and you'd like to
apply special formatting if the data entered is not valid. For example, consider part numbers that consist of sev-
en characters: four uppercase alphabetic characters, followed by a hyphen, and then a two-digit number — for
You can write a conditional formatting formula to determine whether part numbers adhere to this structure, but
the formula is very complex. The following formula, for example, returns TRUE only if the value in A1 meets
the part number rules specified:
=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 cor-
respond 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-21 shows a range that uses the custom INVALIDPART function in a conditional formatting formula.
Cells that contain invalid part numbers have a colored background.
In many cases, you can simply take advantage of Excel's data validation feature, which is described next.
Search JabSto ::

Custom Search