If you just want teh actrivecell then just use
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static iCellColour As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(ActiveCell, Me.Range("A1:H10")) Is Nothing Then
With ActiveCell
Select Case .Interior.ColorIndex
Case 3: .Interior.ColorIndex = 5
Case 5: .Interior.ColorIndex = 6
Case 6: .Interior.ColorIndex = 10
Case Else: .Interior.ColorIndex = 3
End Select
End With
Me.Range("A1").Select
End If
ws_exit:
Application.EnableEvents = True
End Sub
Me refers to the containing object. In userform code, it refers to the
userform, in worksheet event code like this, it refres to the worksheet. I
use this to explicitly reference the correct sheet.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"D.Parker" wrote in message
...
The activecell, hoping that is the best route. Meaning that would stop an
entire row from going through the color sequence? Thanks for you
explanations, I'm still a little lost on the "Me.Range", what does Me
imply?
Thanks again for lending you assistance and expertise to a beginner.
Kind regards,
D.Parker
"Bob Phillips" wrote:
My last post included those comments interspersed in the code at the
bottom.
You can avoid them all doing it by either doing nothing, just the first
cell
in the selected range, or just the activecell. Which would you prefer?
--
HTH
RP
(remove nothere from the email address if mailing direct)
"D.Parker" wrote in message
...
I look forward to reading your comments. I come across information in
the
Help but not specific to things like using "Is Nothing" or "Me.Range"
for
example. I cannot find any descriptions.
Your codes works very well and I would like do use it in other parts
of my
build but I want to make sure I know the operations first.
Also, if I select an entire row (i.e. row 10) and it intersects my
Me.Range("J5:J70"), the entire row goes through the color sequence
changes,
is there a way to avoid that? I'm assuming it is because they
intersect.
I appreciate your assistance.
Kind regards,
D.Parker
"Bob Phillips" wrote:
I'll dissect the code with comments to see if that helps, but this
is
not
real beginner stuff, but it is also not that complex.
Afraid it cannot be simplified in the way you describe, but you
should
find
all of the bits in Help.
"D.Parker" wrote in message
...
Bob:
Thank you very much. Being that I am a beginner/novice user I was
unable
to
find any information on the syntax "Me.Range". How is this used?
Also
the
"Case 3:", does that imply if red then go to blue. The code works
well,
but
I don't understand all the variable names and code movement (i.e.
Not
Intersect...Is Nothing). Or is there a beginner version of this
code
you
have written (meaning all items will either be mentioned in the
Help
menu
or
a textbook). Thank you.
Kind regards,
D.Parker
"Bob Phillips" wrote:
Try it and see :-).
What will happen is that each time you select a cell it will
cycle
through
the colours, as it picks up the current colour and works out the
next
from
this.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"D.Parker" wrote in message
...
Thank you very much. If I reselect the cell at some other
time
will
the
past
color be retained, such that when a user clicks it will start
from
the
last
know color in the case condition?
D.Parker
"Bob Phillips" wrote:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
This is worksheet event code that will be triggered every time a
cell,
or
cells, is selected. The selected cell(s) is passed to the event
macro as
the
Target argument
Static iCellColour As Long
Not necessary, throw-back to some other code.
On Error GoTo ws_exit:
Routine error handling, to force us out on an error
Application.EnableEvents = False
Disabel events so that our code doe not trigger other events.
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing
Then
This tests whether the range that we are monitoring, A1:H10
intersects
with
the Target range passed as the argument to the event macro. This is
a
way of
determining whether the range we are monitorintg has been selected.
If
not,
we just bypass the next set of code.
With Target
Sets a reference to an object so that all subsequent . (dot)
references
implicitly refer to this object type, cuts down on typingt, is more
efficient and more readable
Select Case .Interior.ColorIndex
Initiate a Case statement on the colorindex value of the Target
cell,
that
is the selected cell. This is equivalent to a nested If ... ElseIf
....
End
If statement
Case 3: .Interior.ColorIndex = 5
If the current cell colorindex is 3 (red) set it to 5 (blue)
Case 5: .Interior.ColorIndex = 6
If the current cell colorindex is 5 (blue) set it to 6 (yellow)
Case 6: .Interior.ColorIndex = 10
If the current cell colorindex is 6 (yellow) set it to 10 (green)
Case Else: .Interior.ColorIndex = 3
Any other value, including no colour set to 3 (red)
End Select
End With
Tidy up tand end he Select and With statements
Me.Range("A1").Select
Select A1, so that we can re-select the same cell again.
End If
ws_exit:
Application.EnableEvents = True
Reset events. This is i the error clause, so that if we get an
error, we
always divert here, and always reset events.
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
RP
(remove nothere from the email address if mailing direct)
"D.Parker" wrote in
message
...
Hello:
Is there a way to change the ColorIndex of a given cell
each
time
you
perform a left click with the mouse, within that same
cell? I
was
going
to
setup a loop(s) to cycle through the 4 colors red, blue,
yellow,
and
green
and start the sequence over if the user continues to
click.
Thank
you.
|