why wont these two codes work together

[Colleen45]

These two codes work fine individually, but together (as they are below)I can't get them to work together, and I can't figure out why.

Option Explicit
Rem If cell = "yes" move cursor focus to selected cell
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub ' only one cell changes
If Not Intersect(Target, Range("E191,E197,E202,E212,F231,F233,F235,F251,F254,F260")) Is Nothing Then
    If LCase(Target.Value) = "yes" Then
        Application.EnableEvents = False
        Select Case Target.Address(0, 0)
            Case "E191": Range("G187").Select
            Case "E197": Range("G193").Select
            Case "E202": Range("G200").Select
            Case "E212": Range("G205").Select
            Case "F231": Range("I230").Select
            Case "F233": Range("I232").Select
            Case "F235": Range("I234").Select
            Case "F251": Range("H247").Select
            Case "F254": Range("H247").Select
            Case "F260": Range("H257").Select
        End Select
        Application.EnableEvents = True
    End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With ActiveSheet.Shapes("Rectangle 1")
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width
    .Height = Target.Height
  End With
  Target.Activate
End Sub

 

[Michael M]

Maybe combine them like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub ' only one cell changes
If Not Intersect(Target, Range("E191,E197,E202,E212,F231,F233,F235,F251,F254,F260")) Is Nothing Then
    If LCase(Target.Value) = "yes" Then
        Application.EnableEvents = False
        Select Case Target.Address(0, 0)
            Case "E191": Range("G187").Select
            Case "E197": Range("G193").Select
            Case "E202": Range("G200").Select
            Case "E212": Range("G205").Select
            Case "F231": Range("I230").Select
            Case "F233": Range("I232").Select
            Case "F235": Range("I234").Select
            Case "F251": Range("H247").Select
            Case "F254": Range("H247").Select
            Case "F260": Range("H257").Select
        End Select
        Application.EnableEvents = True
    End If
End If
  With ActiveSheet.Shapes("Rectangle 1")
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width
    .Height = Target.Height
  End With
  Target.Activate
End Sub

 

[Colleen45]

unfortunately this does not work, infact neither of the codes work

 

[Michael M]

Did you put the code in the sheet module ?

 

[Colleen45]

Yes, the worksheet is sheet 2, and that is where I put the code
The original code was in the same worksheet, So I removed that code and replaced it with yours

 

[Michael M]

OK, so are you changing some data in the sheet...it won't fire if nothing has been changed

 

[Colleen45]

What I did was as I mentioned previously, completely remove the code and replaced it with your code. Then I went back to the worksheet and tried to change data, which did not fire to move the cursor. The red border aound the selected cell, does not move when selected with the mouse. I see what you have done in combining the code, but don't understand why they don't want to work together, when they work fine individually.

 

[Michael M]

OK, I'm a little confused....what does the Shape have to do with a red border around the cell ??

 

[Colleen45]

It is a workaround code to change the colour of the border around a selected cell. Shapes is the name of the rectangle used to create a border, because you can't normally change the colour of the border around a selected cell in Excel. The code that is there, works very well.

 

[RoryA]

You're disabling events in the first code, so when it selects a new cell, the SelectionChange code won't run. You need:

Option Explicit
Rem If cell = "yes" move cursor focus to selected cell
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub ' only one cell changes
If Not Intersect(Target, Range("E191,E197,E202,E212,F231,F233,F235,F251,F254,F260")) Is Nothing Then
    If LCase(Target.Value) = "yes" Then
        Select Case Target.Address(0, 0)
            Case "E191": Range("G187").Select
            Case "E197": Range("G193").Select
            Case "E202": Range("G200").Select
            Case "E212": Range("G205").Select
            Case "F231": Range("I230").Select
            Case "F233": Range("I232").Select
            Case "F235": Range("I234").Select
            Case "F251": Range("H247").Select
            Case "F254": Range("H247").Select
            Case "F260": Range("H257").Select
        End Select
    End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With ActiveSheet.Shapes("Rectangle 1")
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width
    .Height = Target.Height
  End With
  Target.Activate
End Sub