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

example, ADSS-09 or DYUU-43.

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.