An Improved Excel Color Banding Solution

Today, someone asked me if it was possible to make Excel highlight the column and the row of the cell that is selected. Excel already highlights the column and row header, but when working on a high-resolution screen, that does not help much in ensuring that you are actually entering data in the right cell.

I set out to find a solution, and on the Microsoft Office Assistance site, I found [1]. While this certainly works, it has a major drawback: any conditional formatting that is applied to any cell in your worksheet is lost. The worksheet for which this solution was needed didn’t actually have any conditional formatting, but I figured that that might change and users would probably never figure out why their conditional formatting was always lost.

The problem with the solution at [1] is that it indiscriminately removes all conditional formatting in the worksheet. I figured that a better solution can be found if you merely keep track of the previously highlighted area. That way, you only need to undo the conditional formatting the macro applied, rather than all of it. In addition, the macro can be improved even further by only removing the conditional formatting that actually matches the conditional formatting it previously set. In other words, only delete the conditional formatting if the formula and the expression matches what the macro itself applies.

That conditional formatting is very simple, by the way. It merely applies a background color (which is guaranteed to be different from any background color the cell already has and from the font color in the cell) if the formula “TRUE” is True, which it always is, of course.

An additional improvement I made is that the last “banding” is removed before the workbook is closed. That way, when you open the workbook next time, there are no oddly highlighted cells. If that would happen anyway, the solution is simple of course: just put the cursor cell that was selected before the workbook was saved and then move it away from that cell.

Here’s the VBA code that achieves this. Just copy and paste it in your workbook’s ThisWorkbook VBA code.

Option Explicit

Private LastTarget As Range

Private Sub Workbook_BeforeClose(Cancel As Boolean)

UndoBanding LastTarget

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal target As Range)

    ' Undo last target band coloring
UndoBanding LastTarget
    ' Save the current target as the last target
    Set LastTarget = target
    ' Band color the current target
    DoBanding target

End Sub

Private Sub UndoBanding(ByVal target As Range)

Dim c As Range
    Dim CurrentRow As Integer, CurrentColumn As Integer
    Dim i As Integer

    If (Not target Is Nothing) Then
        ' Undo in the actual target cell(s)
        If (target.Cells.Count = 1) Then
            UnBandCell target
        Else
            UnBandCell target.Cells(1, 1)
        End If

        ' Un-highlight the same column's cells above
        CurrentRow = target.Cells(1, 1).Row
        CurrentColumn = target.Cells(1, 1).Column

For i = CurrentRow - 1 To 1 Step -1
            UnBandCell target.Worksheet.Cells(i, CurrentColumn)
        Next i

' Un-highlight the same row's cells to the left
        ' TODO: How about other cultures? (R-t-L)
For i = CurrentColumn - 1 To 1 Step -1
            UnBandCell target.Worksheet.Cells(CurrentRow, i)
Next i

    End If

End Sub

Private Sub DoBanding(ByVal target As Range)

    Dim HighlightColor As Variant
    Dim c As Range
    Dim CurrentRow As Integer, CurrentColumn As Integer
    Dim i As Integer

    If (Not target Is Nothing) Then
        HighlightColor = target.Interior.ColorIndex

        ' Ensure that a proper color is selected
        If (HighlightColor < 0) Then
            ' The default is light blue
            HighlightColor = 37
        Else
            ' Add 1 to the color index of the current cell
            HighlightColor = HighlightColor + 1
        End If

' Highlight the actual target cells
If (target.Cells.Count = 1) Then
BandCell target, HighlightColor
Else
BandCell target.Cells(1, 1), HighlightColor
End If

        ' Highlight the same column's cells above
        CurrentRow = target.Cells(1, 1).Row
        CurrentColumn = target.Cells(1, 1).Column

For i = CurrentRow - 1 To 1 Step -1
            BandCell target.Worksheet.Cells(i, CurrentColumn), HighlightColor
Next i

' Highlight the same row's cells to the left
' TODO: How about other cultures? (R-t-L)
For i = CurrentColumn - 1 To 1 Step -1
BandCell target.Worksheet.Cells(CurrentRow, i), HighlightColor
Next i

    End If

End Sub

Private Sub UnBandCell(ByVal cell As Range)

    Dim fc As FormatCondition
    If (Not cell Is Nothing) Then
        ' If this cell has any conditional formatting at all
        If (cell.FormatConditions.Count > 0) Then
            ' Find the conditional formatting this macro applied
            For Each fc In cell.FormatConditions
                ' This is based on the formula and the expression type
                ' Note: in the very unlikely case that someone actually has a use for
' this conditional formatting, it would be deleted also
If (fc.Formula1 = "TRUE" And fc.Type = 2) Then
                    fc.Delete
                End If
            Next
        End If
    End If

End Sub

Private Sub BandCell(ByVal cell As Range, ByVal color As Variant)

' Ensure that the cell's background color is not the same as the color about to be applied
    If (color = cell.Interior.color) Then
        color = color + 1
    End If

    ' Ensure that the cell's font color is not the same as the color about to be applied
If (color = cell.Font.color) Then
color = color + 1
End If

    ' If there are no conditional formattings applied yet
    If (cell.FormatConditions.Count = 0) Then
        ' Apply it
        cell.FormatConditions.Add xlExpression, , "TRUE"
        cell.FormatConditions(1).Interior.ColorIndex = color
    End If

End Sub

Not all is well with this solution. Because four loops have to be executed and conditional formatting is applied on a cell-by-cell basis, slower machines may experience a delay, especially the further away from the A:1 cell the cursor is positioned. Also, if it would so happen that all cells that would be highlighted by this macro already have conditional formatting applied, not a single cell will be highlighted. Of course, this does not cause any harm, but then again, the macro doesn’t do anything to make the spreadsheet easier to use either.

[1]: http://office.microsoft.com/en-us/assistance/HA011366231033.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s