CODEWITHSUNDEEP
vbaexcelvbscript
Dominik
Dominik

Reputation: 4768

Highlighting active cell in Microsoft Excel 2013 when loosing focus

I commonly work with a split screen, comparing two or more Excel sheets or researching information online and then putting it in sheets. Excel nicely highlights the active cell that is clicked, however if I go into the browser or another sheet that highlighting is deactivated, making it hard to remember where you were.

One solution is to add a VBase code that I found somewhere that highlights the active cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Interior.ColorIndex = xlNone
Target.Interior.ColorIndex = 19 'Background Yellow
End Sub

The problem with this is, that, lets say a column was red before I clicked it, when it looses focus now the old red information is gone and its no color. Can we write the information before into a variable, then highlight yello and when we click another cell we give it its original color again? What would be the best solution?

Upvotes: 0

Views: 3323

Answers (2)

Paul McCarthy
Paul McCarthy

Reputation: 886

Try a different spreadsheet program. If Microsoft sees enough people moving away from Excel over this bug then they will fix it. I'd say it is an easy fix for the Excel development team, unless they've all left and gone to Google :-) Google Docs spreadsheet does not have this bug!

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166156

Here's a simple method which is ignoring any pre-existing coloring:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static rng As Range
    If Not rng Is Nothing Then rng.Interior.ColorIndex = xlNone
    Set rng = Target
    rng.Interior.Color = vbYellow
End Sub

Using the Static declaration means that the value of rng isn't lost when the method exits, and is a useful alternative to using a Global.

When you change selection it un-shades the previous selection. However, as you note, it will also not re-apply any shading the range had before you selected it...

This version used a Conditional Formatting approach, which won't replace any existing shading: CF shading overrides any existing fill colors.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static rng As Range

    If Not rng Is Nothing Then rng.FormatConditions.Delete
    Set rng = Target
    With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.Color = 49407
    End With
End Sub

When the CF is removed, any existing shading will be shown as before.

If you might be selecting whole columns of cells, you could consider limiting your operations to only the UsedRange on the sheet.

Upvotes: 1

Related Questions