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