Format Cell with Mouse Click
 

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.

Format Cell with Mouse Click
 

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.
--
Gary's Student


"Ken" wrote:

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.

Format Cell with Mouse Click
 

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

Ken
Gary''s Student wrote:
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.
--
Gary's Student


"Ken" wrote:

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.

Format Cell with Mouse Click
 

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

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

Ken
Gary''s Student wrote:
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.
--
Gary's Student


"Ken" wrote:

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.

Format Cell with Mouse Click
 

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


On 2 Sep 2006 12:13:01 -0700, "Ken" wrote:

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

Ken
Gary''s Student wrote:
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.
--
Gary's Student


"Ken" wrote:

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.

Format Cell with Mouse Click
 

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

(I like to qualify my ranges.)

Ken wrote:

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

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

Ken
Gary''s Student wrote:
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.
--
Gary's Student


"Ken" wrote:

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.



--

Dave Peterson

Format Cell with Mouse Click
 

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 wrote:

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

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

Ken
Gary''s Student wrote:
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.
--
Gary's Student


"Ken" wrote:

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.



--

Dave Peterson

Format Cell with Mouse Click
 

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


Dave Peterson wrote:
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 wrote:

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

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

Ken
Gary''s Student wrote:
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.
--
Gary's Student


"Ken" wrote:

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.



--

Dave Peterson

Format Cell with Mouse Click
 

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


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


Dave Peterson wrote:
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 wrote:

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

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

Ken
Gary''s Student wrote:
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.
--
Gary's Student


"Ken" wrote:

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.



--

Dave Peterson

Format Cell with Mouse Click
 

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



Ken wrote:

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

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


Dave Peterson wrote:
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 wrote:

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

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

Ken
Gary''s Student wrote:
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.
--
Gary's Student


"Ken" wrote:

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.



--

Dave Peterson

--

Dave Peterson

Format Cell with Mouse Click
 

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

Ken wrote:

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

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


Dave Peterson wrote:
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 wrote:

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

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

Ken
Gary''s Student wrote:
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.
--
Gary's Student


"Ken" wrote:

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.



--

Dave Peterson

--

Dave Peterson

Format Cell with Mouse Click
 

OK Dave,

It works great...thanks again.

Ken Woodward

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

Ken wrote:

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

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


Dave Peterson wrote:
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 wrote:

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

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

Ken
Gary''s Student wrote:
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.
--
Gary's Student


"Ken" wrote:

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.



--

Dave Peterson

--

Dave Peterson