Any time you have to manually review worksheet data looking for errors, something is going to slip through. Here's one handy safety net: a user-defined function that will flag one type of invalid data for you.
You're getting some unexpected data type errors from your analysis of imported data. Examination of the first few cells in the worksheet shown in Figure A confirms that some data has been entered as text rather than numbers.
Figure A
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 BooleanIf range.Value <> "" Then
ISNUM = IsNumeric(range.Value)
End If
End Function
- Press [Alt] + Q (Figure B).
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:
Figure C
- Click the Format button.
- Click Green under Color.
- Click OK twice.
Figure D
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.