Hi all

Was wondering if anyone could correct the following so that it actually
works please..


Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRowRange As Range

Set MyRowRange = ("A:N")

If Not Intersect(Target, Range("M6:M3000")) Is Nothing Then


Select Case True

Case Target.Value = "RESTRICTED"

MyRowRange.BackColor = RED

Case Target.Value = "FULL ACCESS"

MyRowRange.BackColor= LIGHT GREEN

Case Target.Value = "LIMITED"

MyRowRange.BackColor= YELLOW

End Select

End If
End Sub

Essential when the sole user of this sheet selects a security access level
for staff members 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".

I know I can do this via the Ribbon, but it will not be available to the
user as he will only have File|Open|Close|Exit|Save available at this stage.

TIA
Mick

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRowRange As Range
Set MyRowRange = ("A:N")

If Not Intersect(Target, Range("M6:M3000")) Is Nothing Then
With MyRowRange
Select Case Target.Value
Case "RESTRICTED": .BackColor = RED
Case "FULL ACCESS": .BackColor= LIGHT GREEN
Case "LIMITED": .BackColor= YELLOW
End Select '//Case Target.Value
End With '//MyRowRange
End If '//Not Intersect
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi there Garry

And thank you for your ongoing help.

The code halted on:

Set MyRowRange = ("A:N")


would it be better to use the FormatR1C1 and have something like:

Set MyRowRange = (C[1], C[14])

or something along those lines

Cheers
Mick

Vacuum Sealed formulated on Friday :
Hi there Garry

And thank you for your ongoing help.

The code halted on:

Set MyRowRange = ("A:N")


would it be better to use the FormatR1C1 and have something like:

Set MyRowRange = (C[1], C[14])

or something along those lines

Cheers
Mick

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

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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)

Thank you to both

That works great.

I truly love coming here as with each visit I learn something new and
helpful...

Appreciate your time.

Regards
Mick

Rick Rothstein presented the following explanation :
@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)

Yes, thanks for pointing that out. I did miss this important detail.
Here's the revised proc...

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 Intersect(MyRowRange, Target.EntireRow).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

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc