45

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:

enter image description here

and Excel:

enter image description here

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?

  • This seems like a decently popular enough question and deserves a better answer than 'use a different app'. A lot of the VBA solutions, though a bit more complex are pretty helpful looking. I think @glenneroo should consider changing the best answer. – CGTheLegend May 9 '17 at 16:09
  • @CGTheLegend: The alternative solutions offered cost money or are temporary macros which AFAIK do not work across multiple files. – glenneroo Oct 25 '17 at 12:24

10 Answers 10

21

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.

13

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.

  • 3
    I don't think this answer merits the best solution. Just using a different application isn't exactly be best solution for everyone. – CGTheLegend May 9 '17 at 16:04
  • I like this solution since MS didn't remove this bug since 100 years – Hartmut P. Nov 13 '18 at 17:02
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
  • @DaveRook the idea of of changing the background color on either SelectionChange or Activate event should work for both Excel and Word. – user222864 Apr 28 '14 at 7:25
6

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).

enter image description here

4

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!

  • Unfortunately this method does not work for Word 2010 and Windows 7 – Eugene Mala Aug 10 '18 at 0:33
2

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:

Screenshot of 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.

0

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.

0

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.

0

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
0

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:

Take a look at the picture here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.