Avoid highlighting rows that are not intended to

Question

I have the following code to highlight in green the row which the user clicks. In the end of the code, I select the cell B6 to deselect the whole row and move the selection above. It is working fine except for the fact that cell B6 is also being highlighted in green and I do not want that. How can I remove that?

In the worksheet I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'If not in the table, exit sub
    If Intersect(Range("Schema"), ActiveCell.EntireRow) Is Nothing Then Exit Sub

    Call ClickInside

End Sub

And in the module:

Sub ClickInside()

    Dim cellno As String: cellno = Str(ActiveCell.row)
    Dim myRow As Range
    Set myRow = ActiveCell.EntireRow 'I want to select the row in the table ONLY

    Call Unprotect_table
    Call MarkRow(cellno, myRow)
    Call Protect_table
End Sub

Sub MarkRow(cellno As String, myRow As Range)

    'Marking that row in green
    Range("Schema").Interior.ColorIndex = 0
    Range("B" & Trim(cellno) & ":I" & Trim(cellno)).Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = RGB(0, 255, 0)
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Application.ScreenUpdating = False

    'Show above
    If Not myRow Is Nothing And CLng(cellno) >= 9 Then
        Range("EditCountry").Value2 = ThisWorkbook.ActiveSheet.Range("B" & Trim(cellno)).Value2
        Range("EditNodeName").Value2 = ThisWorkbook.ActiveSheet.Range("C" & Trim(cellno)).Value2
        Range("EditNodeId").Value = ThisWorkbook.ActiveSheet.Range("D" & Trim(cellno)).Value2
        Range("EditParentNode").Value = ThisWorkbook.ActiveSheet.Range("E" & Trim(cellno)).Value2
        Range("EditParentNodeId").Value = ThisWorkbook.ActiveSheet.Range("F" & Trim(cellno)).Value2
        Range("EditActive").Value = ThisWorkbook.ActiveSheet.Range("G" & Trim(cellno)).Value2
        Range("EditFrom").Value = ThisWorkbook.ActiveSheet.Range("H" & Trim(cellno)).Value2
        Range("EditTo").Value = ThisWorkbook.ActiveSheet.Range("I" & Trim(cellno)).Value2
    End If

    'Move selection
    Range("B6").Select

End Sub

Thanks!

If you don’t want B6 selected and changed - why select it? — Solar Mike, Aug 03 '18 at 07:27
@SolarMike To move the selection to the top. I want to select it but not turn it into green. — MTT, Aug 03 '18 at 07:31
Sorry @jivko I dont know what you mean. Could you explain that? — MTT, Aug 03 '18 at 07:35

score 1 · Accepted Answer · answered Aug 03 '18 at 07:48

Your statement Range("B6").Select triggers the event-routine a second time. To prevent this, use the statement Application.EnableEvents = False:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Range("Schema"), ActiveCell.EntireRow) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Call ClickInside
    Application.EnableEvents = True
End Sub

However, maybe you could have a look to other solution that uses conditional formatting, for example https://stackoverflow.com/a/22350417/7599798

Avoid highlighting rows that are not intended to

1 Answers1