highlight selected cell only

    Greetings Oz'!


    I have a script that changes a cell's interior (fill) color when the cell is selected using the SelectionChange event, if the selected cell lies within a specific range. It also copies the cell's value to another cell.


    [VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range
    Const WS_RANGE As String = "G4:G50" 'target range


    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
    With Target 'if user selects cell in target range
    Range("F4").Value = ActiveCell.Value 'copy selected cell value to F4
    With Selection.Interior 'and change fill colour of selected cell
    .Color = 16777164
    End With
    End With
    End If


    End Sub[/VBA]


    What I would like to figure out is how to change the selected cell's interior colour back to what it was when I click another cell in the targeted range such that only the currently selected cell is highlighted/filled. Any and all suggestions greatly appreciated!

    If the cells in the range could have their colour set to anything, e.g., some are green and some are yellow, then one would have to save their color values before changing (to 16777164) in order to set a cell back to its former colour. I'd probably use a (Scripting) dictionary for that.


    If, on the other hand, you want all cells in the range except the latest selected to have no colour (the default), then just include this line after your If Not Intersect line:

    Code
    Me.Range(WS_RANGE).Interior.ColorIndex = xlColorIndexNone


    Also note that a user could select multiple cells at once, so there may be more than one cell in the Target range.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login