Switch to show that a cell is selected or not

[kishorkhanal]

I have to run query in lot of worksheets everyday. All my worksheets have query at the same cell, say cell A34. Just for convenience, I want to put a color switch in some other cell, say J1. The switch should turn green when cell A34 is selected and should turn red when not selected. Any suggestions?

 

[goldfish]

This code does what I think you are describing. Put this code in each worksheets VBA code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A34")) Is Nothing Then
        Range("J1").Interior.ColorIndex = 3
    Else
        Range("J1").Interior.ColorIndex = 4
    End If
End Sub

HTH,
~Gold Fish

 

[kishorkhanal]

Thank you. Works!!

 

[Smitty]

How's this:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.Address = "$A$34" <SPAN style="color:#00007F">Then</SPAN>
        [J1].Interior.ColorIndex = 4
    Else: [J1].Interior.ColorIndex = 3
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

HTH,

Smitty

EDIT: sorry for the delay, I got caught in a meeting and couldn't post for a bit.