Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
D.Parker
 
Posts: n/a
Default 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.


  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.




  #3   Report Post  
Dana DeLouis
 
Posts: n/a
Default

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.






  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.








  #5   Report Post  
D.Parker
 
Posts: n/a
Default

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.







  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.







  #7   Report Post  
D.Parker
 
Posts: n/a
Default

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.








  #8   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.










  #9   Report Post  
D.Parker
 
Posts: n/a
Default

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.











  #10   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.















  #11   Report Post  
D.Parker
 
Posts: n/a
Default

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.














  #12   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.
















  #13   Report Post  
D.Parker
 
Posts: n/a
Default

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





  #14   Report Post  
Bob Phillips
 
Posts: n/a
Default

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mouse continues to select after clicking on a cell in excel. Fletch Excel Discussion (Misc queries) 4 February 22nd 05 03:27 AM
Change cell back color on click Dave Peterson Excel Discussion (Misc queries) 0 January 24th 05 11:50 PM
moving mouse lighlights cells brentb Excel Discussion (Misc queries) 1 January 17th 05 07:36 PM
moving mouse highlights cells. why? brentb Excel Discussion (Misc queries) 1 January 17th 05 06:34 PM
Undoing LINKS in Excel 2000 jayceejay New Users to Excel 3 January 4th 05 06:58 PM


All times are GMT +1. The time now is 06:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"