Excel change active cell color if within defined range

Question

I am new to VBA. In Excel, I have a defined range called "efficient" which is B2:B10. When a user clicks a cell within that range, I want the cell outline to change red.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Worksheets("LookupLists").Range.Value = ActiveCell.Address
End Sub

what do you mean by "cell outline" ? do you mean the borders ? — Shai Rado

Amorpheuses Amorpheuses · Accepted Answer · 2017-04-25T05:53:53

Place this in the LookupLists code section (which you're presumably doing already):

Private Sub Worksheet_SelectionChange(ByVal target As Range)

  Dim rng As Range
  Set rng = Worksheets("LookupLists").Range("Efficient")

  If Intersect(target, rng) Is Nothing Then Exit Sub

  Application.EnableEvents = False
  target.BorderAround ColorIndex:=3
  Application.EnableEvents = True

End Sub

This will change the border of a cell that is 'clicked' on (ie selected). Note that this will also draw a border around a group of selected cells in that range (ie click, hold and drag mouse) and will also color the cell if you move through the range using arrow keys - which might not be entirely what you're after.

EDIT

To remove the border if you click elsewhere you can do try this instead:

Private Sub Worksheet_SelectionChange(ByVal target As Range)

  Dim rng As Range
  Set rng = Worksheets("LookupLists").Range("Efficient")

  Application.EnableEvents = False
  If Intersect(target, rng) Is Nothing Then
    rng.Borders.Color = xlNone
  Else
    target.BorderAround ColorIndex:=3
  End If
  Application.EnableEvents = True

End Sub

But this will remove the borders for each selection change. Also it's effect won't be really noticeable unless you move up the column.

Excel change active cell color if within defined range

1 Answers