How To Use VBA Code To Mimic Conditional Formatting Of An Excel Table

If you've ever been faced with a table of names and phone numbers it can be hard sometimes to visually see the information you need. That's because the look and feel of the spreadsheet can be bland and unexciting.

One simple technique is to use Excel's conditional formatting to make every second row a different colour; this adds some visual contrast, making it easier to read and comprehend the information.

You can mimic this functionality with your own VBA code and add some of your own enhancements to stamp your visual design on your lists and tables.

VBA Code To Identify Alternate Rows

While conditional formatting is an impressive tool, you might want something a little different:

Highlight every 3rd rowMake every second row boldUnderline every fourth row

The trick here is to find every nth row and do something with the row. The basis for finding alternate rows is this simple line of code, where n is the alternate row number you want to highlight.

If rowNo/n=int(rowNo/n) then
' >>> match found
End if

Because the int function returns only the whole number it implies that when the two sides of the equation are equal then a match has been found. In the following example, we'll try to format a table starting in the first row.

To match every third row the following VBA code would apply:

' select the region and set the alternate row numbers to highlight.
Sheets(1).Activate
Range("a1").CurrentRegion.Select
n = 3

After selecting the current region the code loops through each row and identifies a match. Once a matching row is found the address is saved to the variable myRange and rather than highlight the entire row, we'll just change the cells within the matching row:

For x = 1 To Selection.Rows.Count
If x / n = Int(x / n) Then
myRange=selection.Rows(x).Address

' formatting code goes in here
end if
next

Changing The Formatting Of A Matching Row

How you want to format the rows is up to you but the best way to discover VBA's formatting codes is to record a macro changing the format of a cell and adapt it to do what you need.

'A shaded background

Range(myrange).Interior.ColorIndex = 5

'Make every cell in the row bold

Range(myrange).Font.Bold = true

'Create a line running under every cell in the row

Range(myRange).Borders(xlEdgeBottom).Weight = xlThin

Summary

This article has attempted to show how simple VBA coding can improve the use of one of Excel's core functionalities. If you're printing tables and lists on a regular basis your own conditional formatting with VBA can only add to the professionalism of your work.

Andy L Gibson is a former Web Site programmer rediscovering his interest in software applications for small business.

Previously he has used AJAX, XML and ASPX to program Web Sites for restaurants, auction houses and Heath Authorities. He is keen to answer questions and explore business opportunities whereever they might present themselves. You can talk with Andy at his blog at http://solutions4business.wordpress.com/ including links to other articles on VBA issues he has written.

View the original article here