how do I change color of "active" cell to differentiate from rest

[devildog97]

I want the cell I am working in to be highlighted in a color other then
white. How to I format it as so. Example.... wherever I click on the
worksheet, I want that single cell to be highligted to uniform color.

 

[Gary''s Student]

Try this tiny worksheet event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
Target.Interior.ColorIndex = 6
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[sbitaxi]

That's a cool like formula, I tried it on a worksheet that I am toying
with, but when I selected a cell that had existing formatting, it
cleared it back to white when I clicked away. Is it possible to return
it to the original colour? I've made a brief stab at it, but I'm still
pretty green with code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Dim i As Target.Interior.ColorIndex
If Target.Interior.ColorIndex > 0 Then
Let i = Target.Interior.ColorIndex
Target.Interior.ColorIndex = 6
Cells.Interior.ColorIndex = i
Else
Cells.Interior.ColorIndex = xlNone
Target.Interior.ColorIndex = 6
End If
End Sub


Steven

 

[Gary''s Student]

It is a bit more complex. Whenever we select a cell, before making any
changes, we must first "remember" the cell's address and interior color in
global static variables. That way when we select another cell, we will have
enough information to restore the original's color.

 

[sbitaxi]

I tried to capture ("remember") the interior colour using the variable
i in my formula

If If Target.Interior.ColorIndex > 0 Then
Let i = Target.Interior.ColorIndex

But that doesn't quite do it. So, I need to capture the cell address
Let x = Selection.Range


Still not there. Perhaps the "Let" is not the correct route. I've seen
Dim used in a similar manner, but I don't know what it means.

Thanks for your help!


S

 

[Gary''s Student]

Check this post for an update tomorrow.

 

[Gary''s Student]

Check this out:

Public OldRange As Range
Public OldColor As Integer
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'''''''''''''''''''''''''''''''''''''''
' restore the previous clicked cell
'''''''''''''''''''''''''''''''''''''''
If OldRange Is Nothing Then
Else
OldRange.Interior.ColorIndex = OldColor
End If
'''''''''''''''''''''''''''''''''''''
' next remember the old color
'''''''''''''''''''''''''''''''''''''
Set OldRange = Target
OldColor = Target.Interior.ColorIndex
''''''''''''''''''''''''''''''''''''''
' last set the display color
''''''''''''''''''''''''''''''''''''''
Target.Interior.ColorIndex = 6
End Sub

Note that OldRange and OldColor are declared BEFORE the macro starts.

 

[sbitaxi]

Of course! That makes sense, since the macro takes effect as soon as
you click in a cell. Though, I admit I am still having trouble
wrapping my mind around the macro, but that is just all part of the
learning process.

One final issue, (I am really not trying to complicate things, they
just come up as I work on things) when I select a full column, or a
range of cells where there is a variety of different interior colours,
I get a runtime error '94' Invalid use of Null. I understand why it
happens, but is there a way to prevent it?

Thanks for all your time!


S

 

[Gary''s Student]

Your final issue is completely logical. I should have considered it before:

Public OldRange As Range
Public OldColor As Integer
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count > 1 Then Exit Sub
'''''''''''''''''''''''''''''''''''''''
' restore the previous clicked cell
'''''''''''''''''''''''''''''''''''''''
If OldRange Is Nothing Then
Else
OldRange.Interior.ColorIndex = OldColor
End If
'''''''''''''''''''''''''''''''''''''
' next remember the old color
'''''''''''''''''''''''''''''''''''''
Set OldRange = Target
OldColor = Target.Interior.ColorIndex
''''''''''''''''''''''''''''''''''''''
' last set the display color
''''''''''''''''''''''''''''''''''''''
Target.Interior.ColorIndex = 6
End Sub

 

[sbitaxi]

Ah, just restrict the number of cells the macro works on to 1.

Thank you, a great exercise in learning VB basics.