click event for changing cell color

L

liglin

Hello,

I would like to write a macro that changes the color of a cell to red
when it is clicked - could anyone help me with the click or
onmousedown event?

Thanks,
Liglin
 
C

Chip Pearson

Liglin,

Put the following code in the ThisWorkbook code module.


Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Range)
Static OldCI As Integer
Static OldRng As Range
If Not OldRng Is Nothing Then
OldRng.Interior.ColorIndex = OldCI
End If
OldCI = Target.Interior.ColorIndex
Set OldRng = Target
Target.Interior.ColorIndex = 3 'red
End Sub

You might also be interested in my RowLiner addin at
www.cpearson.com/excel/rowliner.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
D

Don Guillett

right click sheet tab>view code>insert this. Any cell in column 12 will be
colored red when clicked.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 12 Then Exit Sub
Target.Interior.ColorIndex = 3
End Sub
 
B

Bob Phillips

Liglin,

A slight variation that highlights the whole row

Private Sub Worksheet_Selection(ByVal Target As Range)
Dim sRow As String

Cells.FormatConditions.Delete

With Target.EntireRow
sRow = .Address
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=1=1"
.FormatConditions(1).Interior.ColorIndex = 3
End With

End Sub

It is worksheet event code, so on the target worksheet right-click the sheet
tab, select View Code, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
L

liglin lillibli

Thanks Don.

Is there a way to undo the event? Perhaps with a double click or a left
click?

THanks,
Liglin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
B

Bob Phillips

Use it like a toggle

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 12 Then Exit Sub
If Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = xlColorIndexNone
Else
Target.Interior.ColorIndex = 3
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top