Highlight cell by selecting it

Copper Contributor

Is there a way to highlight a cell by just slicking on it?

4 Replies

@BdiveleyHSP 

See for example How to highlight active cell or selection in Excel? 

Please read the notes to learn about the side effects.

@Hans Vogelaar 

Another option is to use conditional formatting rather than an interior fill.  By defining the name 'isSelected?' to refer to

= ISREF(Sheet1!H15  SelectedRange)

[the intersection of the current cell as a relative reference and a range set by the Macro]

one can colour cells using leaving the interior fill unaltered.

 

image.png

The macro is 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Target.Name = "SelectedRange"
End Sub

@Peter Bartholomew 

Hi Peter,

Thanks - I am familiar with this method, but it has been less than 100% dependable in my experience. I have tried it several times, but it occasionally left cells highlighted after another cell had been selected...

Fair enough. It was just an idea that seemed to work on the day, not something I have used to any extent. Naming the Target range seems to be robust but conditional formatting itself seems antiquated and fragile. Something I would like to do is to apply a CF to a named range and have the CF adjust dynamically, also to use an array formula to set the conditional format, rather than it simply using the top-left cell.

Since I use drag and drop as a routine part of building solutions, working with relative referencing is painful.