1 year ago
#379348
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