Conditional formating via VB
Saorry about that! I didn't go past correcting the Select Case
construct.Here 's a tested version...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRowRange As Range
Set MyRowRange = ActiveSheet.Range("A:N")
If Not Intersect(Target, Range("M6:M3000")) Is Nothing Then
With MyRowRange.Interior
Select Case Target.Value
Case "RESTRICTED": .ColorIndex = 3 '//red
Case "FULL ACCESS": .ColorIndex = 35 '//light green
Case "LIMITED": .ColorIndex = 6 '//yellow
Case Else: .ColorIndex = 0 '//no fill
End Select '//Case Target.Value
End With '//MyRowRange.Interior
End If '//Not Intersect
End Sub
@Garry,
You need to change your With statement from this...
With MyRowRange.Interior
to this...
With Intersect(MyRowRange, Target.EntireRow).Interior
because the OP, in his original message, said "I would like it to color the
background of MyRowRange to the applicable color for that specific row that
is being intersected with/by the column "M"
Rick Rothstein (MVP - Excel)
|