Creating A Worksheet Map
In this document I describe how to create a VBA utility that generates a map of the active worksheet. The map is generated on a new worksheet, and it consists of color-coded cells that let you quickly identify values, text, and formulas.
The figure below shows an example of a such a map. Cells that contain text are green, those than contain a numeric value are colored yellow, and cells that contain formulas are colored red. Such a map can help you spot potential errors. For example, if one formula in a block of formulas has been overwritten by a value, that cell will stand out in the map view.
The QuickMap Subroutine
The subroutine that generates the worksheet map is listed below. If you'd like to use this utility, just copy the code and paste it to a VBA module. Then, activate a worksheet and execute the QuickMap subroutine.
Sub QuickMap() If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub ' Create object variables for cell subsets On Error Resume Next Set FormulaCells = Range("A1").SpecialCells _ (xlFormulas, xlNumbers + xlTextValues + xlLogical) Set TextCells = Range("A1").SpecialCells(xlConstants, xlTextValues) Set NumberCells = Range("A1").SpecialCells(xlConstants, xlNumbers) On Error GoTo 0 ' Add a new sheet and format it Sheets.Add With Cells .ColumnWidth = 2 .Font.Size = 8 .HorizontalAlignment = xlCenter End With Application.ScreenUpdating = False ' Do the formula cells If Not IsEmpty(FormulaCells) Then For Each Area In FormulaCells.Areas With ActiveSheet.Range(Area.Address) .Value = "F" .Interior.ColorIndex = 3 End With Next Area End If ' Do the text cells If Not IsEmpty(TextCells) Then For Each Area In TextCells.Areas With ActiveSheet.Range(Area.Address) .Value = "T" .Interior.ColorIndex = 4 End With Next Area End If ' Do the numeric cells If Not IsEmpty(NumberCells) Then For Each Area In NumberCells.Areas With ActiveSheet.Range(Area.Address) .Value = "N" .Interior.ColorIndex = 6 End With Next Area End If End Sub
How it Works
The subroutine first checks to make sure the active sheet is a worksheet. If not, there's a quick exit with no further action. Next, it creates three object variables by using the SpecialCells method to identify the various cell types. The SpecialCells method is very useful. If you're not acquainted with it, I urge you to check it out in Excel's online help file. Notice the use of On Error Resume Next. This is to avoid the error that occurs if no cells qualify -- for example, if the worksheet has no formulas.
Next, the subroutine adds a new worksheet, reduces the cell width, and sets the horizontal alignment to center. This step is just cosmetic. The sub then turns off screen updating to speed things up a bit.
The next three blocks of code process the cells. If no cell qualify, the object variable is Empty, so the sub tests for this. Then, the routine loops through each Area in the Range object and formats the cell. You can easily customize this part of the subroutine to apply different formatting.
Power Utility Pak add-in includes a much more sophisticated version of this
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks