Format Cell with Mouse Click

[Ken]

Given one column: Insert date and a specific color by clicking a cell
in that column with mouse: either right or left...Clicking on any cell
in the column should produce the same results.

 

[Guest]

How about double click?

put this in worksheet code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Target, Columns("C")) Is Nothing Then
Exit Sub
End If
Target.Value = Date
Target.Interior.ColorIndex = 6
End Sub

double click any cell in column C and get the date and a yellow background.

 

[Ken]

Thanks for the help...not working yet...getting syntax error on first
line of code...is something missing?

Ken

 

[Ken]

OK...got the syntax issue resolved...now...how would i be able to do
two columns on the same sheet? i.e, column c in yellow...column e in
red?

Thanks again

 

[Gord Dibben]

You probably got hit by line wrap.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

should be all on one line.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

If Intersect(Target, Columns("C")) Is Nothing Then
becomes
If Intersect(Target, me.range("C")) Is Nothing Then

(I like to qualify my ranges.)

 

[Dave Peterson]

oops.
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("C:C,E:E")) Is Nothing Then Exit Sub
If .Column = 3 Then
.Interior.ColorIndex = 6
Else
.Interior.ColorIndex = 3 'change to what you want
End If
.Value = Date
End With
End Sub

I didn't read the whole followup.

 

[Ken]

Great job Dave...works like a charm. Thanks to everyone who contributed

 

[Ken]

Dave's formula works great..but I need to take it up one more step.
Instead of just working with column C&E I need this to work on
multiple columns. i.e, Col A=Yellow; Col B=Red; Col C=no fill; Col
D=Yellow,Col E=Red; Col F= no fill, etc, etc. All columns with colors
also contain current date. I need to be able to expand this across the
spreadsheet at least thirty columns. Any help with this would be
greatly appreciated.

Thanks

Ken

 

[Dave Peterson]

Etc, etc doesn't really help.

But maybe you can modify this the way you want:



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

With Target
If .Cells.Count > 1 Then Exit Sub
select case .column
case is = 1, 3, 6, 7
.interior.colorindex = 6
case is = 2, 4
.interior.colorindex = 3
case is = 5, 19
.interior.colorindex = 33
end select
End With
End Sub

Just add as many cases as you need--and you can group them by color (I used A,
C, F, G with colorindex 6).

 

[Dave Peterson]

And I wiped out the assignment of the date. Put that back after the "End
Select" statement.

 

[Ken]

OK Dave,

It works great...thanks again.

Ken Woodward