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

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" wrote in message
...
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" 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.

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

Bob


"Dana DeLouis" wrote in message
...
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" wrote in message
...
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" 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.

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

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

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

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.