Rather than manually examine each cell, you can create a function that will mark the invalid cells for you. Follow these steps:
- Open the worksheet containing the imported data and press [Alt] + [F11].
- Go to Insert | Module.
- At the prompt, type the following code:
Function ISNUM(range) As Boolean
If range.Value <> "" Then
ISNUM = IsNumeric(range.Value)
End IfEnd Function
- Press [Alt] + Q (Figure B).
- Select the cells containing the imported data. (For this example, we selected A1:A6.)
- Go to Format | Conditional Formatting, click the drop-down arrow, and select Formula Is. In Excel 2007, select the Home tab, click Conditional Formatting in the Styles group, click New Rule, and select Use A Formula To Determine Which Cells To Format.
- Enter the following formula in the formula text box:
- Click the Format button.
- Click Green under Color.
- Click OK twice.
Miss an Excel tip?
Check out the Microsoft Excel archive and catch up on other Excel tips.
Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.