Articles Tagged: excel

Auto Color Cells July 25, 2005

Many users of Excel have made it common practice to color code cells to help identify inputs, formulas, etc. For example, it is common to color all cells act as hard coded inputs (i.e. not a formula) blue, all formulas black. This Excel macro looks at the contents of each selected cell and sets the color appropriately. Further I have added the green coloring for all external references.

' Set the color of cells to blue or black respectively
' Copyright under GPL by Mark Grimes
' Keyboard Shortcut: Crtl+Shift+C

Sub mgSetColor()
    For Each c In Selection.Cells
        If Left(c.Formula, 1) = "=" Then
            If InStr(c.Formula, ".xls") Or InStr(c.Formula, ".XLS") Then
                c.Font.ColorIndex = 10
            ElseIf InStr(c.Formula, "OFFSET") Then
                c.Font.ColorIndex = 9
            Else
                allNumbers = True
                For i = 1 To Len(c.Formula) - 1
                    If (Asc(Mid(c.Formula, i, 1)) < 40) Or (Asc(Mid(c.Formula, i, 1)) > 61) Then
                        ' MsgBox "Setting false: " &amp; Mid(c.Formula, i, 0) &amp; " = " &amp; Asc(Mid(c.Formula, i, 1))
                        allNumbers = False
                        Exit For
                    Else
                        ' MsgBox Mid(c.Formula, i, 1) &amp; " = " &amp; Asc(Mid(c.Formula, i, 1))
                    End If
                Next
                If allNumbers Then
                    c.Font.ColorIndex = 5   ' blue
                Else
                    c.Font.ColorIndex = 0   ' auto
                End If
            End If
        Else
            c.Font.ColorIndex = 5
        End If
    Next
End Sub

Align Center July 25, 2005

I hate merged cells. They create all sorts of problems adding/deleting columns, filling down, etc. But it can look nice to have text centered across a range not just a single cell. Luckily, Excel provides the rarely used Align Center formatting option. This macro provides easy access to toggling the alignment formatting across all selected cells… but that’s not all… :-) it also centers the contents of a single cell if that is all that is selected.

' Toggles Align Center
' Copyright under GPL by Mark Grimes
' Keyboard Shortcur: Crtl+Shift+A
'
Sub mgCenterAlign()
    If Selection.count = 1 Then
        With Selection
            If .HorizontalAlignment = xlHAlignCenter Then
                .HorizontalAlignment = xlGeneral
            Else
                .HorizontalAlignment = xlHAlignCenter
            End If
        End With
    Else
        With Selection
            If .HorizontalAlignment = xlCenterAcrossSelection Then
                .HorizontalAlignment = xlGeneral
            Else
                .HorizontalAlignment = xlCenterAcrossSelection
            End If
        End With
    End If
End Sub

Create Spacing Rows July 25, 2005

I often want to have some space between row to call attention to a particular row, but rather than having a full row, a small row would work better. This macro will adjust the height of all the select cells if they are empty.

' Set the height of all blank selected rows to small
' Copyright under GPL by Mark Grimes
' Keyboard Shortcur: Crtl+Shift+E 
'
Sub mgShrinkSpaces()
    For Each c In Selection.Cells
        If c.Value = "" Then
            c.RowHeight = 5
        End If
    Next
End Sub