Rick Rothstein used his keyboard to write :
Rick, see my fix. It doesn't require Format() <IMO!

You are right... the Format function call is not required. I had made an
error, wrote code (incorrectly) to correct the problem, then modified that
and ended up posting it in haste without testing it. Anyway, I am glad you
were able to follow my logic and make the correction on your own... good job.
Now, let's make it into what I consider a "true" one-liner and remove the
If..Then housing...

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A" & Target.Row).Resize(1, 14).Interior.ColorIndex = Split( _
"0 3 35 6")(-(Not Intersect(Target, Range("M6:M3000")) Is Nothing) * _
(InStr(1, " ,RESTRICTED ,FULL ACCESS,LIMITED ", _
Target.Value, 1) \ 11))
End Sub

Note that I constructed the InStr function (originally and now still) to
allow the words to be typed with any letter casing on the assumption the user
is typing the words in and might type Limited instead of LIMITED. However, if
data validation is being used on the cells in Column M so that the casing
will always be upper case, then we can shorten the code slightly....

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A" & Target.Row).Resize(1, 14).Interior.ColorIndex = Split( _
"0 3 35 6")(-(Not Intersect(Target, Range("M6:M3000")) Is Nothing) * _
(InStr(" ,RESTRICTED ,FULL ACCESS,LIMITED ", _
Target.Value) \ 11))
End Sub

Rick Rothstein (MVP - Excel)

Good job, Rick! Either one looks great to me. Personally, I prefer non
case sensitive. Thanks for the exercise, ..I appreciate your persistant
effort!

--
Garry

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