click event for changing cell color

[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

 

[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

 

[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

 

[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)

 

[liglin lillibli]

Thanks Don.

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

THanks,
Liglin

 

[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)