Tab movement using Tab Key. Active Cell Color Change

[DarrenBurke]

Hi Guys and Ladies

I have an Array for using the Tab Key. I would like the color (colour... for english UK) of the active cell to change as the user tabs through the sheet. Basically to see what cell he/she is currently on.

My code for the sheet. Tab key works perfect, a bit of bling would be nice Cheers and thank you for the help.

Private Sub TabOrder()
     
     'Set applicable sheet name
    Const TabSheet = "PICKUP COPY1"
    Dim Limit As Integer, NumPos As Integer
    Dim NextPos As String, MyCel As String
    Dim MyTO
     
     'Set tab offset for other sheets
    If ActiveSheet.Name <> TabSheet Then
        ActiveCell.Offset(0, 1).Select
        Exit Sub
    End If
     'Set your cells in desired order
    MyTO = Array("C7", "C6", "E4", "C8", "C10", "C12", "C14", "C16", "C18", "C20", "C22", "C24", "D24", "E24", "C26", "C32", "F34", "A36", "I7", "I6", "K4", "I8", "I10", "I12", "I14", "I16", "I18", "I20", "I22", "I24", "J24", "K24", "I26", "I32", "L34", "G36", "O7", "O6", "Q4", "O8", "O10", "O12", "O14", "O16", "O18", "O20", "O22", "O24", "P24", "Q24", "O26", "O32", "R34", "M36", "S46")
    Limit = UBound(MyTO)
    MyCel = ActiveCell.Address(0, 0)
     'Check for match in array
    On Error GoTo LastLine
    NumPos = Application.WorksheetFunction.Match(MyCel, MyTO, 0)
     'Return to first cell
    If NumPos = Limit Then
        NextPos = MyTO(1)
    Else
        NextPos = MyTO(NumPos + 1)
    End If
    Range(NextPos).Activate
    Exit Sub
LastLine:
     'Set tab offset if named cell not found
    ActiveCell.Offset(0, 1).Select
End Sub

 

[DarrenBurke]

Just give me maybe tonight to work on it at home... If anyone else in the meantime wants to give a go they are welcome...

Some issues encountered is if I apply the code it removes the yellow highlighting you have in place already so will need to investigate a workaround for that

Thats ok Jimmy. It can just tab along in Yellow. Not to worry about current cell yellow highlighting

 

[DarrenBurke]

Just give me maybe tonight to work on it at home... If anyone else in the meantime wants to give a go they are welcome...

Some issues encountered is if I apply the code it removes the yellow highlighting you have in place already so will need to investigate a workaround for that

I dont see my reply, reply again.... Thats ok Jimmy it can just tab in yellow. no highlighting needed on sheet. Cheers and Thank You!

 

[Jimmypop]

I dont see my reply, reply again.... Thats ok Jimmy it can just tab in yellow. no highlighting needed on sheet. Cheers and Thank You!

Hi Darren

Please try following and let me know if anything went wonky...

Cherryl PickupMaster.xlsm

 

[DarrenBurke]

Hi Darren

Please try following and let me know if anything went wonky...

Cherryl PickupMaster.xlsm

Perfect Jimmy you Champion. Thank you for your time and expertise

 

[Jimmypop]

Lol... I am also still just learning but glad could help...will give explanation shortly of what I did

 

[DarrenBurke]

Lol... I am also still just learning but glad could help...will give explanation shortly of what I did

 

[Jimmypop]

Ok...



1. On your Sheet2 mirror to restore the sheet7 after capture what I did is I added conditional formatting to those yellow highlighted cells only which meant they will not be affected by using Cells.Interior.ColorIndex = xlNone. I also when this pastes to sheet7 it pastes the conditional formatting as well.

2. Then I added the code below which basically unprotects the sheet7, after which the red formatting is applied to the active cell and when another cell is selected then it locks the sheet again, unlocks it and applies the formatting again...etc.

3. This achieves more or less what you are looking for but might be cumbersome and it is still open to anyone if they have any other suggestions on this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Application.ScreenUpdating = False
     Worksheets("PICKUP COPY1").Unprotect " 1"
      Cells.Interior.ColorIndex = xlNone
      ActiveCell.Interior.ColorIndex = 3
    Worksheets("PICKUP COPY1").Protect " 1"
 Application.ScreenUpdating = True
End Sub

 

[DarrenBurke]

Ok...



1. On your Sheet2 mirror to restore the sheet7 after capture what I did is I added conditional formatting to those yellow highlighted cells only which meant they will not be affected by using Cells.Interior.ColorIndex = xlNone. I also when this pastes to sheet7 it pastes the conditional formatting as well.

2. Then I added the code below which basically unprotects the sheet7, after which the red formatting is applied to the active cell and when another cell is selected then it locks the sheet again, unlocks it and applies the formatting again...etc.

3. This achieves more or less what you are looking for but might be cumbersome and it is still open to anyone if they have any other suggestions on this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Application.ScreenUpdating = False
     Worksheets("PICKUP COPY1").Unprotect " 1"
      Cells.Interior.ColorIndex = xlNone
      ActiveCell.Interior.ColorIndex = 3
    Worksheets("PICKUP COPY1").Protect " 1"
 Application.ScreenUpdating = True
End Sub

Its Perfect Jimmy thank you so much. I will tune the pallette number . Champion

 

[Peter_SSs]

Hi Darren

Please try following and let me know if anything went wonky...

Cherryl PickupMaster.xlsm

@Jimmypop
In future, please post the code directly in the forum as per #4 of the Forum Rules, not just a link to a sample file.
It is okay to also provide a link to a sample file as well, but the solution should also be able to be found directly in the forum.

@DarrenBurke
In future, lease mark the post that actually answered your question. In this case I have moved the mark for you from post 18 to post 17

 

[Jimmypop]

Hi Peter

Apologies for this... I thought the code that I added to assist OP in post #17 was all that was required... the link is his whole project with a lot of other code that was not relevant to the question... hence I linked it back to him in #13 and just explained what I did in #17 and then provided the code that was added to his existing sheet... Will in future post entire code of sheet