test for click in cell

  • Thread starter Thread starter goneil
  • Start date Start date
G

goneil

Dear Excel community,

I have put a border around cells A1, A2, A3, A4.

Just by clicking in a cell I would like "just" that cell to go:

A1 to go Red, A2 Green, A3 Amber, A4 Blue.

When I click in a cell set the other 3 cells to blank

Thank you to the bright spark that can figure this out.

Cheers
George
 
Hi

Try the following
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, tr As Long
Set rng = Range("A1:A4")
If Not Intersect(Target, rng) Is Nothing Then
tr = Target.Row
rng.Interior.ColorIndex = xlNone
Select Case tr
Case 1
Target.Interior.ColorIndex = 3
Case 2
Target.Interior.ColorIndex = 4
Case 3
Target.Interior.ColorIndex = 44
Case 4
Target.Interior.ColorIndex = 41
Case Else
End Select
End If
End Sub

--
Regards
Roger Govier

goneil said:
Dear Excel community,

I have put a border around cells A1, A2, A3, A4.

Just by clicking in a cell I would like "just" that cell to go:

A1 to go Red, A2 Green, A3 Amber, A4 Blue.

When I click in a cell set the other 3 cells to blank

Thank you to the bright spark that can figure this out.

Cheers
George

__________ Information from ESET Smart Security, version of virus
signature database 4527 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4527 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
First install the following macro in a standard module and run it:

Sub Macro1()
ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:="",
SubAddress:= _
"Sheet1!A1", TextToDisplay:=" "
ActiveSheet.Hyperlinks.Add Anchor:=Range("A2"), Address:="",
SubAddress:= _
"Sheet1!A2", TextToDisplay:=" "
ActiveSheet.Hyperlinks.Add Anchor:=Range("A3"), Address:="",
SubAddress:= _
"Sheet1!A3", TextToDisplay:=" "
ActiveSheet.Hyperlinks.Add Anchor:=Range("A4"), Address:="",
SubAddress:= _
"Sheet1!A4", TextToDisplay:=" "
End Sub

This just sets up some hyperlinks in A1 thru A4

Then install the following worksheet event macro in the worksheet code area:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
i = Right(Target.Parent.Address, 1)
Dim r As Range, r2 As Range
Set r2 = Range(Target.Parent.Address)
Set r = Range("A1:A4")
r.Interior.ColorIndex = xlNone
red = 3
green = 10
yellow = 6
blue = 5
With r2.Interior
If i = 1 Then .ColorIndex = red
If i = 2 Then .ColorIndex = green
If i = 3 Then .ColorIndex = yellow
If i = 4 Then .ColorIndex = blue
End With
End Sub
 
Back
Top