Colour by clicking cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ronoee - 28 Jan 2007 15:30 GMT

I am searching for a way to change background colour by click in a cell and
back to normal colour by clicking it again.
Is this possible? And if how is it don?

Thank you in advance.

Reply to this Message

Gary''s Student - 28 Jan 2007 17:11 GMT

How about double-click?

This is a demo for cell B2:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Target, Range("B2")) Is Nothing Then
Exit Sub
End If
If Target.Interior.ColorIndex = 6 Then
Target.Interior.ColorIndex = xlNone
Else
Target.Interior.ColorIndex = 6
End If
Cancel = True
End Sub

This goes in worksheet code, not a standard module.

Signature

Gary's Student
gsnu200702

> I am searching for a way to change background colour by click in a cell and
> back to normal colour by clicking it again.
> Is this possible? And if how is it don?
>
> Thank you in advance.

Reply to this Message

ronoee - 28 Jan 2007 18:57 GMT

Thank you for helping me out,
I tried this out but it dos not seems to work.
Please give me some further instructions. I am not familiar with using VBA.
Double-click is ok.

"Gary''s Student" skrev:

> How about double-click?
>
[quoted text clipped - 20 lines]
> >
> > Thank you in advance.

Reply to this Message

Gary''s Student - 28 Jan 2007 19:10 GMT

Very easy. Just right-click the tab at the bottom of the window and select
View Code...

Then just paste the stuff in and close the window.

Signature

Gary's Student
gsnu200702

> Thank you for helping me out,
> I tried this out but it dos not seems to work.
[quoted text clipped - 27 lines]
> > >
> > > Thank you in advance.

Reply to this Message

JLatham - 28 Jan 2007 17:16 GMT

Excel doesn't trap for a single click, if you are looking to just change
colors of certain cells, you can use the worksheet's before double-click
event handler to do this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'change fill color of cell C3 if it is double-clicked
If Application.Intersect(Target, Range("C3")) Is Nothing Then
Exit Sub ' was not cell C3
End If
If Target.Interior.ColorIndex = 3 Then ' 3 is red
Target.Interior.ColorIndex = 5 ' make it blue
Else
Target.Interior.ColorIndex = 3 ' set to red
End If
Cancel = True ' cancel the double-click
End Sub

When you double-click on the cell (C3 in this case) the color will toggle
between red and blue. If you'd like to toggle between 'no fill' (white) and
another color, change the = 5 above to = xlNone

If you are looking for something that would work on any cell, take a look at
Chip Pearson's RowLiner add-in: http://www.cpearson.com/excel/RowLiner.htm

> I am searching for a way to change background colour by click in a cell and
> back to normal colour by clicking it again.
> Is this possible? And if how is it don?
>
> Thank you in advance.

Reply to this Message

ronoee - 28 Jan 2007 18:57 GMT

Thank you for helping me out,
I tried this out but it dos not seems to work.
Please give me some further instructions. I am not familiar with using VBA.
Double-click is ok.

"JLatham" skrev:

> Excel doesn't trap for a single click, if you are looking to just change
> colors of certain cells, you can use the worksheet's before double-click
[quoted text clipped - 26 lines]
> >
> > Thank you in advance.

Reply to this Message

Gord Dibben - 28 Jan 2007 19:32 GMT

Both sets of code are event code and operate on only the one cell.

Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

If you want a larger range for this code to work on try this amended code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As _
Boolean)
Const WS_RANGE As String = "A1:A10"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Target.Interior.ColorIndex = 6 Then
Target.Interior.ColorIndex = xlNone
Else
Target.Interior.ColorIndex = 6
End If
Next
Cancel = True
End If
End Sub

Gord Dibben MS Excel MVP

>Thank you for helping me out,
>I tried this out but it dos not seems to work.
[quoted text clipped - 33 lines]
>> >
>> > Thank you in advance.

Reply to this Message

ronoee - 28 Jan 2007 20:13 GMT

Exactly something like what I was looking for, but I need to work inside a
larger area like B2:H30, and I would like it to change from the colour green
(the format of the background colours of the cells before changing) and then
to read is this possible.
And thank you for helping a complete novice in this matter.

"Gord Dibben" skrev:

> Both sets of code are event code and operate on only the one cell.
>
[quoted text clipped - 58 lines]
> >> >
> >> > Thank you in advance.

Reply to this Message

Gord Dibben - 28 Jan 2007 20:28 GMT

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As _
Boolean)
Const WS_RANGE As String = "B2:H30"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Target.Interior.ColorIndex = 10 Then
'10 is dark green, 4 is bright green
Target.Interior.ColorIndex = 3
Else
Target.Interior.ColorIndex = 10
End If
Next
Cancel = True
End If
End Sub

Gord

>Exactly something like what I was looking for, but I need to work inside a
>larger area like B2:H30, and I would like it to change from the colour green
[quoted text clipped - 66 lines]
>> >> >
>> >> > Thank you in advance.

Reply to this Message

ronoee - 28 Jan 2007 20:42 GMT

It works perfect.
Thank you very much for helping me out.

"Gord Dibben" skrev:

> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As _
> Boolean)
[quoted text clipped - 84 lines]
> >> >> >
> >> >> > Thank you in advance.

Reply to this Message

Gord Dibben - 29 Jan 2007 00:07 GMT

Thanks for the feedback.

You got a lot of good suggestions on this project from several people.

Working together we cobbled up something appropriate.

Gord

>It works perfect.
>Thank you very much for helping me out.
[quoted text clipped - 89 lines]
>> >> >> >
>> >> >> > Thank you in advance.

Reply to this Message

Bob Phillips - 28 Jan 2007 19:17 GMT

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "H10" '<=== change to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Interior.ColorIndex = 38 Then
.Interior.ColorIndex = xlColorIndexNone
Else
.Interior.ColorIndex = 38
End If
.Offset(0, 1).Activate
End With
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Signature

HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> I am searching for a way to change background colour by click in a cell and
> back to normal colour by clicking it again.
> Is this possible? And if how is it don?
>
> Thank you in advance.

Reply to this Message

ronoee - 28 Jan 2007 19:56 GMT

Thank you for trying to help me out here.
I tried this out and it works, changing all cells colours marking all cells
by clicking upper left corner of rows and columns. It is however coming up
with a failure “Run-time error ‘1004’: Application-defined or
object-defined error.
But it is not quit what I was looking for.
I need to change cells one by one in a cretin aria inside the sheet by
clicking the particular cell that is to bee changed and then back to standard
colour by clicking again.
And to bee frank I have no knowledge about VBA.

"Bob Phillips" skrev:

> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Const WS_RANGE As String = "H10" '<=== change to suit
[quoted text clipped - 23 lines]
> >
> > Thank you in advance.

Reply to this Message