1 year ago

#379348

test-img

Luis Lopes

Change VBA code to work with protect worksheet

This code was kindly given by VBasic2008. It works very well with the unprotected worksheet but when i protect the worksheet, the code generates an error. I didn't mention the protected worksheet when i asked help to my code. How to make changes for the code to work with the protected page?

Option Explicit

Private lTarget As Range
Private FirstPassed As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Const FirstRow As Long = 16
    Const Cols As String = "I:J"
    Const iColor As Long = 9359529
    
    Dim rrg As Range
    Set rrg = Rows(FirstRow).Resize(Rows.Count - FirstRow + 1)
    Dim irg As Range: Set irg = Intersect(rrg, Target)
    If Not irg Is Nothing Then Set irg = Intersect(irg.EntireRow, Columns(Cols))
    
    If FirstPassed Then
        If irg Is Nothing Then
            If Not lTarget Is Nothing Then
                lTarget.Interior.ColorIndex = xlNone
                Set lTarget = Nothing
            End If
        Else
            If Not lTarget Is Nothing Then
                lTarget.Interior.ColorIndex = xlNone
            End If
            irg.Interior.Color = iColor
            Set lTarget = irg
        End If
    Else
        rrg.Columns(Cols).Interior.ColorIndex = xlNone
        If Not irg Is Nothing Then
            irg.Interior.Color = iColor
            Set lTarget = irg
        End If
        FirstPassed = True
    End If

End Sub

In the same away, dbmitch gave me a similar code that have the same problem.

Dim lTarget As Range
Const TargetCol1    As Integer = 9
Const TargetCol2    As Integer = 10

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    If Target.Row >= 6 Then
        If Not lTarget Is Nothing Then
            lTarget.EntireRow.Interior.ColorIndex = 0
        End If
        
        Cells(Target.Row, TargetCol1).Interior.Color = 9359529
        Cells(Target.Row, TargetCol2).Interior.Color = 9359529
        
        Set lTarget = Target
    End If
End Sub

excel

vba

cell

worksheet

0 Answers

Your Answer

Accepted video resources