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: " & Mid(c.Formula, i, 0) & " = " & Asc(Mid(c.Formula, i, 1)) allNumbers = False Exit For Else ' MsgBox Mid(c.Formula, i, 1) & " = " & 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
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
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
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