Highlight row and insert text on click

[J.W. Aldridge]

If user clicks on any cell in column J, i need for that cell to state
"done" and the active row (A:J) to highlight/change background to
gray.

If they click again, remove the highlight and word.

 

[CompleteNewb]

If user clicks on any cell in column J, i need for that cell to state
"done" and the active row (A:J) to highlight/change background to
gray.

If they click again, remove the highlight and word.

Like 50% of anything I do, this is a bastardization of someone's
bastardization of Chip Pearson's code. I think it originally was meant as a
sheet change, rather than selection change. I'm sure there's a better way
to specify the J column (alert; I only went down to 65000), and there's also
no error handling for an accidental multiple selection; also, to make the
event work twice you have to click outside the cell and click back on it;
clicking on it once and then again without leaving it won't trigger the 2nd
event. Also keep in mind that this assumes you either want "Done" or
nothing; there's no allowing for other values when clicking in J.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("J1:J65000")) Is Nothing Then
If Selection.Value <> "DONE" Then
Selection.Value = "DONE"
Range("A" & Selection.Row & ":J" & Selection.Row).Interior.ColorIndex =
56
Else
Selection.Value = ""
Range("A" & Selection.Row & ":J" & Selection.Row).Interior.ColorIndex =
xlNone
End If

End If
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

Excel doesn't really keep track of clicks this way. You can use the selection
change event, but that'll fire when the user uses the mouse or selects the cell
with his arrow keys.

Instead of using a click, you could use a rightclick or doubleclick event to do
the work.

This uses the beforerightclick event and goes in that sheet's module.

Option Explicit
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Dim RngToInspect As Range

If Target.Cells.Count > 1 Then
Exit Sub 'one cell at a time
End If

Set RngToInspect = Me.Range("J1").EntireColumn

If Intersect(Target, RngToInspect) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False 'just in case
With Target
If LCase(Target.Value) = LCase("done") Then
'wipe it out
.Value = ""
.EntireRow.Range("A1:J1").Interior.ColorIndex = xlNone
Else
.Value = "done"
.EntireRow.Range("A1:J1").Interior.ColorIndex = 15
End If
End With
Application.EnableEvents = True

'stop rightclick menu from appearing
Cancel = True

End Sub

A colorindex of 15 was light gray in my workbook. You may want to record a
simple macro to see what the number is for your favorite shade of gray.

 

[J.W. Aldridge]

Thanx Guys!
When I make my first Million, I'm buying you both a Krystal (or White
Castle) with Cheese!