Imagine that you are preparing data for a pivot table, and you want to make sure that all the cells contain the correct data type.
We know that a date or number entered as text can mess things up so if we had a quick way to identify these problems, it could save a lot of time and effort.
This VBA routine allows us to quickly identify such issues.
We have data laid out like this, and everything looks ok.
But, some of those dates are actually text, and so are some of the numbers.
The VBA color codes each of the data types differently so you can easily see where the problems are.
- Numbers are light blue
- Formulae are yellow
- Text is green
- Dates are mid-blue
How the Code Works
The VBA uses the SpecialCells method to create a range object for each of the groups of cells containing formulae, text and numbers.
As dates are numbers in Excel, the numbers range contains both what we would consider numbers, and dates.
By using the Areas property of each of these ranges, we can set the background color, which in VBA is the cell's Interior.ColorIndex, to a color of our choosing.
I've created a form to allow the user to check which type of data they want highlighted. If a check box is checked, that data type is highlighted. If the check box is not checked, then cells containing that corresponding data type has no background color. I've included a Reset button to allow users to quickly clear all check boxes and cell colors.
It's always a good idea to create a copy of your data before working on it like this, if for nothing other than you will lose any colors you have applied to cells.
Choosing Colors for Highlighting
Colors are specified by numeric values, and I used David McRitchie's post on the Excel color palette to choose mine.
I've hard coded the values into four variables, and tried to choose ones that won't burn your eyes.
You can of course choose your own colors.
A nice enhancement would be to use a color picker on the form, but I think this is another blog topic.
I found code by John Walkenbach to create a worksheet map and what I have written here is based on that.
You can download a sample workbook containing this code.
Sharing is Caring
If you liked this, please click the buttons below to share it with your friends and colleagues.