Excel: Nice Shades

If you are using conditional formatting to highlight cells, you can run into problems when you delete/add rows. Sometimes the conditional formatting rules apply to a strange set of cells instead of one contiguous range of cells.

To get around this issue, you can use a macro to do the highlighting. Open the VBA editor (with Alt-F10) and then add the following code to Microsoft Excel Objects --> ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'shade cell with "yes"

If Target.Row > 1 And Target.Column = 2 Then
    If Target.Value = "yes" Then
        Cells(Target.Row, Target.Column).Interior.ColorIndex = 50
    Else
        Cells(Target.Row, Target.Column).Interior.ColorIndex = 2
    End If
End If

This code is run every time you edit a cell. The code makes sure the current cell is not the 1st row (which is usually for headings). It then checks if the cell is in column B (which is column number 2). If the contents of the cell are "yes" it gets green shading, otherwise is gets white shading.

The only downside to this method is that it runs only after editing a cell. Therefore, if you already have  cells with information, you have to re-edit each cell to get the macro to run. This can be done somewhat easily by hitting F2, then the Enter key. For large amounts of data, you need to write a wrapper macro that you can run once on the sheet.