Microsoft Office Tutorials and References

In Depth Information

**Chapter 19: Conditional Formatting and Data Validation**

Figure 19-23:
Using a custom VBA function to apply conditional formatting to cells that contain a formula.

The following conditional formatting formula applies formatting to cell A1 if it contains a date and

the month is June:

=AND(CELLHASDATE(A1),MONTH(A1)=6)

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 seven 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: