Morning Nigel,
OK, so the reason that B10s color is changing is because of the order in
which the events get fired. I've added two Debug lines so it's clear what's
going on. Put a Breakpoint on both Debug lines (select the line and press
F9) add some data to A10 and tab out of the cell. The code will 'pause' at
the first breakpoint and you can then step through one line at a time (using
F8).
The result is that B10 is selected (which is the normal condition) the
Change event fires first on A10 and U10 is then selected. This prompts the
first SelectionChange event to be fired on U10 , but importantly before the
Change procedure has completed (and the rngPrev becomes B10 as that was the
cell that was previously selected). The Change event then finishes off and
then the SelectionChange event is fired once more to deal with the B10
selection change.
Have a go stepping through and you'll see what I mean.
Dim rngPrev As Range
Dim idxPrev As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Target.Count 1 Then Exit Sub
Debug.Print "SelectionChange Event- " & Target.Address
Application.ScreenUpdating = True
If Not rngPrev Is Nothing Then
rngPrev.Interior.ColorIndex = idxPrev
End If
Set rngPrev = Target
idxPrev = rngPrev.Interior.ColorIndex
rngPrev.Interior.ColorIndex = 3
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'player 1
Debug.Print "Change - Event" & Target.Address
If Target.Address = "$A$10" Then Range("U10").Select
If Target.Address = "$C$10" Then Range("w19").Select
If Target.Address = "$E$10" Then Range("Y28").Select
If Target.Address = "$G$10" Then Range("AA37").Select
If Target.Address = "$I$10" Then Range("ac46").Select
End Sub
Anyway, have a go instead with the code below,
Private rngPrev As Range
Private idxPrev As Long
Private m_SkipChange As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If m_SkipChange = False Then
If Not rngPrev Is Nothing Then
rngPrev.Interior.ColorIndex = idxPrev
End If
Set rngPrev = Target
idxPrev = rngPrev.Interior.ColorIndex
rngPrev.Interior.ColorIndex = 3
Else
m_SkipChange = False
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'player 1
With Target
If .Address = "$A$10" Then
Range("U10").Select
m_SkipChange = True
Set rngPrev = Range("U10")
End If
If .Address = "$C$10" Then
Range("W19").Select
m_SkipChange = True
Set rngPrev = Range("W19")
End If
If .Address = "$E$10" Then
Range("Y28").Select
m_SkipChange = True
Set rngPrev = Range("Y28")
End If
If .Address = "$G$10" Then
Range("AA37").Select
m_SkipChange = True
Set rngPrev = Range("AA37")
End If
If .Address = "$I$10" Then
Range("AC46").Select
m_SkipChange = True
Set rngPrev = Range("AC46")
End If
End With
End Sub
Best regards
John
"Nigel" wrote in message
...
yes they are being declared , there are no formulas in any of the cells,
here is the full color change code
Option Explicit
Dim rngPrev As Range
Dim idxPrev As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Target.Count 1 Then Exit Sub
Application.ScreenUpdating = False
If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex = idxPrev
Set rngPrev = Target
idxPrev = rngPrev.Interior.ColorIndex
rngPrev.Interior.ColorIndex = 3
End Sub
if you try that and combine it with the other code you will see what I
mean
When you enter data in a10 then exit it the cursor will initially move to
b10 then onto u10 but it will change the color of b10 and not u10
Thanks
if the user enters a value in c10 and hits tab or enter the purpose is to
have the focus shift to cell u10 and change the color,
"John" wrote:
Hello Nigel,
I'm afraid I'm still not quite clear on what's happening. It's a bit
difficult to tell what's happening with C10 as I can't see the whole code
(have you declared the rngPrev somewhere else)? Also do you have any
formula in C10? In any case, can I just confirm what you "actually" want
to
happen rather than what's happening now.
As I understand it:
If you change the data in A10, you want to select U10 and highlight U10
yellow
If you change the data in C10, you want to select W10 and highlight W10
yellow
If you change the data in E10, you want to select Y10 and highlight Y10
yellow
If you change the data in G10, you want to select AA10 and highlight AA10
yellow
If you change the data in I10, you want to select AC10 and highlight AC10
yellow
So....
a) Is that correct?
b) Is your list restricted to those five ranges or might there by others?
Let me know.
Best regards
John
"Nigel" wrote in message
...
If data is changed in cell A10 I want it to go to cell U10 etc,
stepping
thru the code line by line this is what happens
enter cell A10 enter a number
hit tab
cursor moves to c10 changes color of that cell
cursor jumps to u10 changes color of cell
cursor jumps back to c10 changes color of cell
if I remove the color change code then if I stpe thru the code this is
what
happens
enter cell A10 enter a number
hit tab
cursor moves to c10
cursor jumps to u10
I am trying to get it to saty in cell U10 and have that change color
thanks
"John" wrote:
Nigel,
I'm wasn't clear from your origianl post as to whether you want to
move
the
selected cell after entering data into a specific cell OR after a
specific
cell was selected and not data/format change to place.
So at the moment the reason it jumps back to C10 after the color
changes
in
U10 is because that is a change event (ie not just data).
So on what basis do you want the changes to take place?
Best regards
John
PS Did you read my reply to your original post :
http://groups.google.co.uk/group/mic...567267c13f6263
"Nigel" wrote in message
...
Again so sorry I used your code and it works great, i added another
sub
and
here is that
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex =
idxPrev
Set rngPrev = Target
idxPrev = rngPrev.Interior.ColorIndex
rngPrev.Interior.ColorIndex = 6
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'player 1
If Target.Address = "$A$10" Then Range("U10").Select
If Target.Address = "$C$10" Then Range("w19").Select
If Target.Address = "$E$10" Then Range("Y28").Select
If Target.Address = "$G$10" Then Range("AA37").Select
If Target.Address = "$I$10" Then Range("ac46").Select
end sub
the problem is this
I enter a value in A10 and hit tab the cursor moves to c10 and then
onto
u10
changes the color of u10 correctly but jumps back to c10. do you
have
any
idea why it may be doing this
"Don Guillett" wrote:
AND SO ON
no need to SHOUT! I'm still confused since mind reading is not one
of
my
talents but, in the absence of a better explanation from you, in
the
worksheet_CHANGE event.
=if target.address="$d$3" then range("c3").select
=if target.address="$d$5" then range("gxxx").select
and so on
I'm sure it could be simplified.
--
Don Guillett
SalesAid Software
"Nigel" wrote in message
...
not sure what more info is needed, user exits cell d3 I would
like
cursor
to
go to d33
then they can select cell d4 make an entry and cursor will then
go
to
c3
then they select d5 make an entry and on exiting it will go to
gXX
and
so
on
Cells are paired for example A10 is related to U10m B10 is
related
to
W19
AND SO ON
"Don Guillett" wrote:
You can do this with a worksheet_change macro in the sheet code.
right click sheet tabview codeleft window select
worksheetright
select
worksheet_change
more info needed but this is the basic idea
target.offset(30).select
--
Don Guillett
SalesAid Software
"Nigel" wrote in message
...
Is it possible to program so that when a users exits a cell
he/she
is
forced
to another cell, ie when leaving d3 the cursor will got to d33
I know about cell protection etc but that will not do what I
would
like
Thanks