Improving readability of worksheet data in Excel

It can be a big help if your data in Excel is easy to read (avoiding errors in calculations and or data analysis). I know there are more ways than one to achieve that goal, in this article I’ll show you some code that handles the solution without using conditional formatting. It’s a rough and simple solution…

To address the problem visually look at the differences between the images below…

The original layout above.

The new and improved layout below 😉

For those of you that are not familiar with VBA / macro’s use the steps below…

  • If you use the latest version of Excel make sure you’ve got the “Developer” tab in Excel
  • Next press the button “Record Macro”
  • Then press the button “Stop Recording”
  • Press ALT + F8
  • Choose “Edit”
  • Copy and paste the code below (or download a text file containing the code here…)
  • Save your Excel !!!
  • Press ALT + F8 and Run the Macro
Sub ImproveReadability()
‘
‘ ImproveReadability Macro
‘
‘ Developer: Winko Erades van den Berg
‘ E-mail : winko at winko-erades.nl
‘ Developed: 10-10-2011
‘ Modified: 10-10-2011
‘ Version: 1.0
‘
‘ Description: Improve the readability of your Excel worksheet data by using colors’Declare variables
Dim iRow As Long
Dim iCol As Long
Dim HeaderRow as Integer’Constant values
HeaderRow = InputBox(“Specify the row that contains the header information:”) ‘Create Input Box to ask the user where the header starts
iCol = ActiveSheet.UsedRange.Columns.Count ‘Determine how many columns are in use

‘Change de header lay-out
ActiveSheet.Range(ActiveSheet.Cells(HeaderRow, 1), ActiveSheet.Cells(HeaderRow, iCol)).Select
With Selection.Interior ‘Fill Color
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0
.PatternTintAndShade = 0
End With

With Selection.Font ‘Font Color
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True ‘Font Bold

‘Start highlighting alternate rows
For iRow = HeaderRow + 2 To ActiveSheet.UsedRange.Rows.Count Step 2
ActiveSheet.Range(ActiveSheet.Cells(iRow, 1), ActiveSheet.Cells(iRow, iCol)).Select
With Selection.Interior ‘Fill Color
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.75
.PatternTintAndShade = 0
End With
Next iRow

End Sub

Suggestions for improving this article are welcome, please let me know and drop me a line.