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
Sven Aelterman 