How to keep selected text highlighted when focus changes in Microsoft Word/Excel

Question

Related to this question: Show cell selection in Excel when not in focus except that my case concerns Microsoft Word.

When I select text or a column/row in Microsoft Word or Excel (2003, 2007, 2010 or 2013) it shows up highlighted like this in Word:

and Excel:

However, when I change focus to another program, the highlighted text is no longer highlighted.

I generally work with 2 monitors, where one monitor contains a document/spreadsheet containing data I need to read, and the other monitor is where I am actually doing work. I will select some text (or cells) to make it stand out amongst the many pages of data, and then switch programs, but the highlighted text is no longer visible.

Is there a permanent solution to this problem?

Answer 1

A quick way to fix half of your problem (when switching from Excel to Word) is to copy the text. When you hit Ctrl + c the cells will continue to be marked (the highligthing is gone, but you still have a dotted line around the text).

A downside to this, is that the text are only marked, as long as you hold the cells copied. Meaning you can't copy anything else or the marking is gone. Unfortunately this will not work from Word to Excel.

Answer 2

This seems to be an exclusive Microsoft "feature". For most projects I only need read-only access and formatting doesn't matter in my case, therefore I have switched to OpenOffice which does not exhibit this behavior.

Answer 3

This VBa will do it but it assumes you are NOT using highliting. If you are, your highlighting will get messed up so don't use it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveCell.Worksheet.Cells.Interior.ColorIndex = xlNone
    ActiveCell.EntireRow.Interior.ColorIndex = 19
End Sub

How do I add VBA in MS Office?

---

As a massive work around (if the highlighting doesn't work for you), you could use something like JRuler (hear me out :) !! ) as this will allow you to leave the ruler on screen with the row in question above it so when your eyes return to that screen you can see where you were (so you can see, I lost my highlighting but at least I can see I was looking at row 3)!

Don't get me wrong, this is laborious and a pain if you're doing this a lot, but, it may suffice for any program (Word and Excel).

Answer 4

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 5

After ten years of this problem driving me nuts as well I have finally found a solution that works for me. Unfortunately it is not free... Actual Window Manager has a feature called "Ignore deactivation" which can be enabled for specific apps. This makes the window think it still has focus even when it doesn't. I have this turned on for all office apps and now I can still see the highlighted selection even when another window has focus. I have not had any negative side effects either. I also use multiple monitors and this program has a ton of other useful tools for working with multiple monitors as well that made it worthwhile for me. I installed the trial and then uninstalled it and it offered me 30% off. There may be a free program that does this but I couldn't find one... I hope this helps someone as much as it helped me!

Answer 6

I was struggling with this same issue for a long time. I too get lost in dense Excel files when using multiple screens.

There's a huge collection of Excel plugins available on the internet. I found out that Kutools Excel extension (the free version) has a one-click on/off highlight cross-hair that remains visible even if Excel is unfocused. Kutools also seems to have a lot of additional features, but it certainly isn't the only option available.

Here's a screenshot of the Kutools highlight cross-hair in action:

I do know some people are afraid of 3rd party plugins or tools, but using them is probably the fastest and easiest option.

Answer 7

Insert a text box that stretches the length of the cells. Type a row of ******* similar characters to fill the text box. You can move the text box down the page as you would a ruler or piece of paper on a hard copy. Delete when done.

Answer 8

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 an answer by @Dave; The complex solution was brought together from many sources, especially with the help of @JohnColeman in this post.}

Answer 9

A very simple way of doing this is with conditional formatting and VBA.

Just add this to your ThisWorkbook code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Application.CutCopyMode = False Then
        Application.Calculate
    End If
End Sub

and run the following code only once, to create the conditional format rules:

Sub CreateConditionalFormats()
  Dim y As FormatCondition
    For Each ws In ThisWorkbook.Worksheets
        DoEvents

        'Optionally delete any existing conditional formats
        'ws.Cells.FormatConditions.Delete

        ' Selection highlight
        Set y = ws.Cells.FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(ROW()=CELL(""row""), COLUMN()=CELL(""col""))")
        With y.Borders(xlTop)
            .LineStyle = xlSolid
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With y.Borders(xlBottom)
            .LineStyle = xlSolid
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With y.Borders(xlLeft)
            .LineStyle = xlSolid
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With y.Borders(xlRight)
            .LineStyle = xlSolid
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        y.StopIfTrue = False
        y.SetFirstPriority

        ' Row highlight
        Set y = ws.Cells.FormatConditions.Add(Type:=xlExpression, Formula1:="=ROW()=CELL(""row"")")
        With y.Borders(xlTop)
            .LineStyle = xlDash
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With y.Borders(xlBottom)
            .LineStyle = xlDash
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        y.StopIfTrue = False

        ' Column highlight
        Set y = ws.Cells.FormatConditions.Add(Type:=xlExpression, Formula1:="=COLUMN()=CELL(""col"")")
        With y.Borders(xlLeft)
            .LineStyle = xlDash
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With y.Borders(xlRight)
            .LineStyle = xlDash
            .Color = -16776961
            .TintAndShade = 0
            .Weight = xlThin
        End With
        y.StopIfTrue = False
    Next
End Sub

Answer 10

Using insert shape/rectangle is a simple workaround. Select "No Fill" option and than you can even edit cells through it.

Take a look at the picture here:

Answer 11

It's crazy that this question had to be asked in the first place, but since Microsoft doesn't seem to be ínclined to fix it, here's a solution that's as stupid as the problem.

RDP (remote desktop) into another PC, and start the 'source' copy of Excel/Word -- the one where you want the selection to remain visible -- there. Run another instance locally on your own PC.

While the selection in the remote instance will not be hidden when you change the focus from the remote to a local window (just make sure the Word/Excel window remains the active one inside the RDP session), you can still use the clipboard etc.

You can make use of the drive sharing feature of RDP to give the remote machine access to the file you want to open, directly on your own machine.