Each Click of the Mouse

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.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static iCellColour As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
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

'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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

Here's a slightly different twist to Bob excellent idea:

With Target.Interior
Select Case .ColorIndex
Case 3, 5, 6, 10
.ColorIndex = 59727 Mod (2 * .ColorIndex + 7)
Case Else
.ColorIndex = 3
End Select
End With

--
Dana DeLouis
Win XP & Office 2003


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Static iCellColour As Long
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
> With Target
> 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
>
> '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" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>>
>
>

Where the hell did you drag that up from :-)?

Bob


"Dana DeLouis" <[email protected]> wrote in message
news:[email protected]...
> Here's a slightly different twist to Bob excellent idea:
>
> With Target.Interior
> Select Case .ColorIndex
> Case 3, 5, 6, 10
> .ColorIndex = 59727 Mod (2 * .ColorIndex + 7)
> Case Else
> .ColorIndex = 3
> End Select
> End With
>
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > Static iCellColour As Long
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
> > With Target
> > 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
> >
> > '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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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.
> >>
> >>
> >
> >
>
>

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)
> Static iCellColour As Long
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
> With Target
> 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
>
> '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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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)
> > Static iCellColour As Long
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
> > With Target
> > 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
> >
> > '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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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)
> > > Static iCellColour As Long
> > > On Error GoTo ws_exit:
> > > Application.EnableEvents = False
> > > If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
> > > With Target
> > > 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
> > >
> > > '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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> > > >
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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.
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Now how do you add more colors?

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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.
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in
message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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.
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Bob:

The activecell works perfectly! Thanks a million! So, Me.Range is the same
as Worksheets("Sheetname").Range. Thanks for that information also.

If I am at L65 and I click the mouse, after the click I return to L2, before
I can click to the next color. I commented out Me.Range("L2").Select and the
cursor stays on L65, but I have to move to another cell and come back to L65
to cycle through the color sequence. Can I make the cursor stay on the
current cell and cycle thhrough the sequence without returning to L2? The
code follows:

If Not Intersect(ActiveCell, Me.Range("L9:L65")) Is Nothing Then
With ActiveCell
Select Case .Interior.ColorIndex
Case 6:
.Interior.ColorIndex = 4
.HorizontalAlignment = xlCenter
.Font.Bold = True
.Value = "P"
Case 4:
.Interior.ColorIndex = 3
.HorizontalAlignment = xlCenter
.Font.Bold = True
.Value = "F"
Case Else:
.Interior.ColorIndex = 6
.Value = ""
End Select
End With
'Me.Range("L2").Select
End If

Kind regards,

D.Parker

"Bob Phillips" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected]> wrote in
> message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > 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.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >

The problem is that if you do not select off of L65 (to say L2), then you
cannot select back onto it to force the event macro to fire as the click on
the activecell has no effect.. Selecting off is not good I agree, but I have
never found a better way.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"D.Parker" <[email protected]> wrote in message
news:[email protected]...
> Bob:
>
> The activecell works perfectly! Thanks a million! So, Me.Range is the
same
> as Worksheets("Sheetname").Range. Thanks for that information also.
>
> If I am at L65 and I click the mouse, after the click I return to L2,
before
> I can click to the next color. I commented out Me.Range("L2").Select and
the
> cursor stays on L65, but I have to move to another cell and come back to
L65
> to cycle through the color sequence. Can I make the cursor stay on the
> current cell and cycle thhrough the sequence without returning to L2? The
> code follows:
>
> If Not Intersect(ActiveCell, Me.Range("L9:L65")) Is Nothing Then
> With ActiveCell
> Select Case .Interior.ColorIndex
> Case 6:
> .Interior.ColorIndex = 4
> .HorizontalAlignment = xlCenter
> .Font.Bold = True
> .Value = "P"
> Case 4:
> .Interior.ColorIndex = 3
> .HorizontalAlignment = xlCenter
> .Font.Bold = True
> .Value = "F"
> Case Else:
> .Interior.ColorIndex = 6
> .Value = ""
> End Select
> End With
> 'Me.Range("L2").Select
> End If
>
> Kind regards,
>
> D.Parker
>
> "Bob Phillips" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in
message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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" <[email protected]> wrote in
> > message
> > > > > > > > > >
news:[email protected]...
> > > > > > > > > > > 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.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >