Colour by clicking cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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





--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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 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:
 
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:
 
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
 
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
 
Back
Top