Switch to show that a cell is selected or not

kishorkhanal

Active Member
Joined
Mar 23, 2006
Messages
434
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?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
This code does what I think you are describing. Put this code in each worksheets VBA code:
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
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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.
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,694
Latest member
dpatete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top