Thursday, May 29, 2008

Color code an Excel spreadsheet

I received a very large spreadsheet to be converted to GAMS. To quickly get a feeling how many formulas there are and where they are located, this code colors all cells with formulas. The color depends on the type of the cell (number, string, logical or error).

Sub color()
ActiveSheet.UsedRange.Style = "Normal"
Call colorsub(xlNumbers, "Accent1")
Call colorsub(xlTextValues, "Accent2")
Call colorsub(xlErrors, "Accent3")
Call colorsub(xlLogical, "Accent4")
End Sub
Sub colorsub(SpecialCells As Integer, styletype As String)
Dim r As Range
On Error GoTo err
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, SpecialCells)
r.Style = styletype
End Sub

An earlier version used HasFormula on each individual cell. As the spreadsheet I am working on is very large, that approach was very slow. This version returns complete ranges and is much faster.