Show cell selection in Excel when not in focus

Question

It's really annoying that Excel (2003 and 2007) doesn't show what cell, row or column that is selected when the window is not in focus. I typically want to refer to the current cell or row while working in another application.

Is there any workaround or fix that will make the cell/row highlighted when not in focus? I know that you can copy a cell (Ctrl+C) but it's kind of tiresome to do that every time.

Answer 1

I think there is a work around but, it really depends on your situation!

You can create a macro which fires when the selection changes and it simply changes the background of each cell. When you 'leave' the cell it will reset the row's background value to white and then select the new row.

I added this to my Sheet1 in the Visual Basic window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.ColorIndex = xlColorIndexNone
    ActiveCell.EntireRow.Interior.ColorIndex = 34
End Sub

This screen shot was taken while the application had lost focus.

This may be annoying but you could easily add a button which could toggle this feature on or off!

The negatives are (from top of my head : It will remove any current highlighting you have. So if you have highlighting on your page (cells coloured) then best not use this! Also, it will probably print with the highlighted rows!

Answer 2

Here is a modification of the code from @datatoo. It reads the previous values to prevent losing the current fill color. It also changes the text color to make it further stand out. I added it to the Excel sheet in the code editor (Alt-F11 from Excel).

Click here for information on making a worksheet change event.

'VBA code for Excel to show active cell in worksheet when worksheet is out of focus

Dim wasActive As String
Dim originalFillColor As String
Dim originalTextColor As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Set up colors on load
    If wasActive = Empty Then
        wasActive = "A1"
        originalFillColor = Range(wasActive).Interior.Color
        originalTextColor = Range(wasActive).Font.Color
    End If

    'Reset previous cell to original color values; If statement prevents removal of grid lines by using "0" for clear fill color when white
    If originalFillColor = 16777215 Then
        Range(wasActive).Interior.ColorIndex = "0"
        Range(wasActive).Font.Color = originalTextColor
    Else
        Range(wasActive).Interior.Color = originalFillColor
        Range(wasActive).Font.Color = originalTextColor
    End If

    'Set new colors and change active cell to highlighted colors (black fill with white text)
    originalFillColor = ActiveCell.Interior.Color
    originalTextColor = ActiveCell.Font.Color
    wasActive = ActiveCell.Address
    ActiveCell.Interior.ColorIndex = "1"
    ActiveCell.Font.ColorIndex = "2"

End Sub

Answer 3

You can do something like this if you need to. Although it might be sheet specific

Dim wasActive As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If wasActive = Empty Then wasActive = "A1"
Range(wasActive).Interior.ColorIndex = "0"
ActiveCell.Interior.ColorIndex = "6"
wasActive = ActiveCell.Address
End Sub

This changes what is not active back to white, and changes the activecell to yellow. and still shows when the window is not active. Not sure this is the best way, but it works

Answer 4

In Excel 2003 the currently selected cell address is shown in the upper left. Although the cell isn't highlighted, it gets you partway there.

Answer 5

Use a shape to highlight the selection.

Note: It works only when switching to another Excel window. As a workaround you can open an empty Excel window and switch to this window before switching to another application to keep the highlight.

Just add this to your ThisWorkbookcode (your workBOOK, not your sheet´s code). This will work for every sheet in your workbook.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    DeleteSelectionHighlight
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    DeleteSelectionHighlight
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
  On Error Resume Next
    Dim shp As Shape
    Application.ScreenUpdating = False

    Set shp = ActiveSheet.Shapes("SelectionHighlight")
    If Err.Number <> 0 Then
        Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 1, 1, 1, 1)
        With shp 'Format shape to your preference
            .Name = "SelectionHighlight"
            .Line.ForeColor.RGB = RGB(226, 0, 0) ' Border color
            .Line.Weight = 1.5
            .Line.DashStyle = msoLineSolid
            .Fill.Visible = msoFalse 'No background
            '.Fill.ForeColor.RGB = RGB(0, 153, 0) 'Background color
            '.Fill.Transparency = 0.95 'Background transparency
        End With
    End If

    Dim oldZoom As Integer
    oldZoom = Wn.Zoom
    Wn.Zoom = 100 'Set zoom at 100% to avoid positioning errors
    With shp
        .Top = Wn.Selection.Top   'Tweak the offset to fit your desired line weight
        .Left = Wn.Selection.Left 'Tweak the offset to fit your desired line weight
        .Height = Wn.Selection.Height
        .Width = Wn.Selection.Width
    End With
    Wn.Zoom = oldZoom 'Restore previous zoom
    Application.ScreenUpdating = True
End Sub

Private Sub DeleteSelectionHighlight()
  On Error Resume Next
  Dim shp As Shape
    Set shp = ActiveSheet.Shapes("SelectionHighlight")
    shp.Delete
End Sub

You can even format the shape to your preference by tweaking the code.

The advantages are:

• you don´t lose your original formatting when excel crashes of you have a power failure
• You don´t lose your original formatting when using CTRL+[ from another workbook that changes the active sheet
• You don´t lose the highlight when making changes to the other excel window while compared to the CTRL+C solution

Answer 6

there is no permanent solution to this problem.

a workaround (may get annoying in a while) would be to change the highlighting of the selected cells while they are selected and re-select them again to drop the color.

Stick this code in Sheet1 code behind and go to you spreadsheet and select some cells, select other ones then re-select the first ones to drop the color

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cell As Range

    For Each cell In Target.Cells
        If cell.Interior.Color = RGB(60, 150, 230) Then
            cell.Interior.Pattern = xlNone
        Else
            cell.Interior.Color = RGB(60, 150, 230)
        End If
    Next
End Sub

Answer 7

A Simple Solution ^{_{that places a cell color when the selection changes}}

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Selection.Interior.ColorIndex = xlColorIndexNone
    Selection.Interior.Color = RGB(204, 204, 204)
End Sub

---

A Complex Solution ^{_{that only changes the cell color when focus is lost}}

In a standard module:

Option Explicit    
Public s As Range

In the sheet(s) you want it to work in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set s = Selection
End Sub

In ThisWorkbook:

Private Sub Workbook_Deactivate()
    If s Is Nothing Then
        Set s = Selection
        Exit Sub
    End If
    s.Interior.ColorIndex = xlColorIndexNone
    s.Interior.Color = RGB(204, 204, 204)

    ' This is optional formatting to make the cells look more like they're actually selected
    s.Borders.Color = RGB(130, 130, 130)
    s.BorderAround _
    Color:=RGB(30, 130, 37), Weight:=xlThick
End Sub

Private Sub Workbook_Activate()
    If s Is Nothing Then
        Set s = Selection
        Exit Sub
    End If
    s.Interior.ColorIndex = xlColorIndexNone
    s.Borders.ColorIndex = xlColorIndexNone
End Sub

---

_{Citations: The simple solution is based off of a previous answer by @Dave; The complex solution was brought together from many sources, especially with the help of @JohnColeman in this post.}