CODEWITHSUNDEEP
vbaexcel
David G
David G

Reputation: 2347

Highlight current Selection in Yellow

I simply want the selected range to be yellow, and to return to colorless when it is de-selected. The code is the following:

Option Explicit
Public previouscell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Selection.Interior.Color = vbYellow
    previouscell.Interior.ColorIndex = xlNone
    Set previouscell = Selection
End Sub

The problem is setting previouscell in the first place. I tried putting it in Worksheet_Activate(), but it wouldn't work as soon as I opened the workbook (only when I changed the sheet, it would work great after that.)

So I tried declaring it as a public in ThisWorkbook.Workbook_open as well: 

Option Explicit
Public previouscell As Range
Private Sub Workbook_Open()
    Set previouscell = ActiveCell
    ActiveCell.Interior.Color = vbYellow
End Sub

But it doesn't recognize the variable previouscell then, because I believe it is not transferred from ThisWorkbook (I did close and reopen the workbook before testing). Does anyone know what I need to change for this simple task?

Upvotes: 1

Views: 1146

Answers (4)

AgentRev
AgentRev

Reputation: 799

(4th edit)

Use this workbook code, with your sheet code removed:

Option Explicit
Public previousCells As Range

Private Sub Workbook_Open()
    SetSelectionYellow
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    SetSelectionYellow
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    SetSelectionYellow
End Sub

Private Sub SetSelectionYellow()
    If Not previousCells Is Nothing Then previousCells.Interior.ColorIndex = xlNone
    Set previousCells = Selection
    previousCells.Interior.Color = vbYellow
End Sub

Upvotes: 2

Tim
Tim

Reputation: 2892

In the ThisWorkbook.Workbook_Open sub, set previouscell to A1 (or some default cell that has no interior color). Then in the Worksheet_SelectionChange sub do your thing. You have everything correct, but you needed 2 parts:1) setting the initial default value for previouscell and 2) changing the interior of cells when the selection changes. You did part 2, but missed part 1.

Upvotes: 0

findwindow
findwindow

Reputation: 3153

This seems to work if you place it in the code module of the sheet in question.

Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.ColorIndex = xlNone
    ActiveCell.Interior.Color = vbYellow
End Sub

Upvotes: 2

basodre
basodre

Reputation: 5770

You're on the right track with the first code block. Just test to see if previouscell has been set to anything, and if not, set it to the current selection (or whatever you want to do).

Option Explicit
Public previouscell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If previouscell Is Nothing Then
        Set previouscell = Selection
    End If

    Selection.Interior.Color = vbYellow
    previouscell.Interior.ColorIndex = xlNone
    Set previouscell = Selection
End Sub

Upvotes: 1

Related Questions