Formatting Worksheet Elements

---

Now that you have the Excel color system well in hand, you can get to work changing the appearance of the elements of your worksheet elements. There are two elements you can change at the window and worksheet level: gridlines and sheet tabs. In the default Excel workbook configuration, the gridlines are a medium gray. If you want to change that color to better fit your design, you can do so by setting the color to a custom RGB value or a color constant using the ActiveWindow.GridlineColor property or, if you want to assign a color from the Excel color palette, the ActiveWindow.GridlineColorIndex property. A benefit of using the GridlineColorIndex property is that you will have the ability to change the gridlines back to the default color by setting the property's value to the VBA constant xlColorIndexAutomatic.

As an example, the following procedure changes the gridlines to blue, then to white (which makes the gridlines invisible), and then changes them back to the automatic color:

Sub CycleGridlines()
    MsgBox ("Changing the gridline color to blue.")
    ActiveWindow.GridlineColorIndex = 5
    MsgBox ("Changing the gridline color to white.")
    ActiveWindow.GridlineColor = RGB (255, 255, 255)
    MsgBox ("Changing the gridline color back to the default color.")
    ActiveWindow.GridlineColorIndex = xlColorIndexAutomatic
    MsgBox ("Ending the procedure.")
End Sub

Note 

The GridlineColorIndex property Help topic displays the default colors of the Excel color palette.

The other worksheet-level element you can change is the sheet tab. The sheet tabs appear on the tab bar at the bottom left of the Excel window. The tabs are normally white with black lettering when active and a neutral gray color when inactive, but you can highlight one or more of them by changing their color using either the Worksheet.Tab.ColorIndex or Worksheet.Tab.Color property. For example, if you wanted to change the tab of any worksheet where a user changes the existing data, you could do so by placing the following event procedure in the code module associated with each worksheet you want to monitor:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    ActiveWorkbook.ActiveSheet.Tab.ColorIndex = 5
End Sub

For more information on Excel events in general, and what does or does not trigger the Worksheet_Change event in particular, see Chapter 12, 'Understanding and Using Events.'

Figure 10-1 shows two worksheets with sheet tabs that were changed using the Worksheet_Change event procedure. The worksheet on the left is active, so Excel displays a line of the tab's color below the worksheet name, but notice that Excel displays the worksheet's name in black type on a white background so that the name can be read easily. The inactive sheet to the right displays the tab with a full blue background and black text, indicating it was also changed.

Figure 10-1: The active sheet's name is displayed with a view to readability, whereas inactive sheet tabs let the tab's color take precedence.

Important 

You need to be sure to put the code for a worksheet event, such as Worksheet_Change, in the code module associated with the worksheet you want to monitor.

---