MrExcel Publishing
Your One Stop for Excel Tips & Solutions

If the mouse clicks a cell


Posted by Keith on January 31, 2002 8:57 AM

How can I make an If statement or some code: if the user clicks the cell, the cell's interior color will be red. I tried a few thing but they didn't work:
Private Sub range("d12")_click()

range("d12").Interior.Color = vbRed

End Sub
-----------------------------------------------------

OR:
Function keith()

If range("d12").Activate = False Then
range("d12").Comment.Visible = True
End If

If range("d12").Activate = True Then
range("d12").Comment.Visible = False
End If

End Function


in the If statement "range("d12").active" automatically make the cell active even with the if statement.

I was wondering if anybody had any ideas.


Posted by Tom Urtis on January 31, 2002 9:19 AM

You have a couple options, such as BeforeDoubleClick and BeforeRightClick, but this comes close too, using SelectionChange. The only thing is, it will shade cell D12 red not only if someone clicks on it but if they arrow on over to it, which maybe you'd have wanted anyway? Maybe not? See if this works for you...right click on your sheet tab, left click on View code, and paste this in. It toggles the color on and off with the clicks too, which can be changed if need be.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("D12")) Is Nothing Then
If Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = 3
End If
End If
End Sub


HTH

Tom Urtis

range("d12").Interior.Color = vbRed


Posted by JohnG on January 31, 2002 9:24 AM

Try This
'Save this in ThisWorkbook
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Sheet1" Then ActiveCell.Interior.ColorIndex = 3
End Sub
This is set to only affect sheet1 How can I make an If statement or some code: if the user clicks the cell, the cell's interior color will be red. I tried a few thing but they didn't work: range("d12").Interior.Color = vbRed

Posted by JohnG on January 31, 2002 9:30 AM

Kieth see my solution , there is a SheetSelectionChange option

: How can I make an If statement or some code: if the user clicks the cell, the cell's interior color will be red. I tried a few thing but they didn't work


Posted by Keith on January 31, 2002 10:07 AM

Re: Kieth see my solution , there is a SheetSelectionChange option

Thank you Tom and John the code works great!