I have been sent this little ditty. Apparently it allows you to tur
cells a certain colour depending on the value entered in it.
Conditional formatting does not only limits you to 3 choices.

Can anyone explain it to me & how to get started as I have not used th
VB part of Excel much.

Many thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B7:U29")) Is Nothing Then
Select Case Target.Text
Case Is = "H"
Target.Interior.ColorIndex = 3
Case Is = "M"
Target.Interior.ColorIndex = 45
Case Is = "L"
Target.Interior.ColorIndex = 6
Case Is = "U"
Target.Interior.ColorIndex = 41
Case Is = "N"
Target.Interior.ColorIndex = 50
Case Else
Target.Interior.ColorIndex = xlNone
End Select
End If
End Su

--
GlenS1
-----------------------------------------------------------------------
GlenS18's Profile: http://www.excelforum.com/member.php...nfo&userid=945
View this thread: http://www.excelforum.com/showthread.php?threadid=26270

Basically, every time something changes in that sheet, the
code below will be run.

It first checkes whether the range being changed is in
cells B7:U29

If it is, it looks at the value of the changed cell. The
value within the cell determines the colour it is changed
to.

Hope this helps

Rgds

Rog



-----Original Message-----

I have been sent this little ditty. Apparently it allows
you to turn
cells a certain colour depending on the value entered in
it.
Conditional formatting does not only limits you to 3
choices.

Can anyone explain it to me & how to get started as I
have not used the
VB part of Excel much.

Many thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B7:U29")) Is Nothing Then
Select Case Target.Text
Case Is = "H"
Target.Interior.ColorIndex = 3
Case Is = "M"
Target.Interior.ColorIndex = 45
Case Is = "L"
Target.Interior.ColorIndex = 6
Case Is = "U"
Target.Interior.ColorIndex = 41
Case Is = "N"
Target.Interior.ColorIndex = 50
Case Else
Target.Interior.ColorIndex = xlNone
End Select
End If
End Sub


--
GlenS18
----------------------------------------------------------
--------------
GlenS18's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=9457
View this thread:
http://www.excelforum.com/showthread...hreadid=262706

.

To get this operational for a given worksheet it must be in that specific
sheet's code module. The easier way to get it there is to copy it and then
right-click the worksheet's tab and pick View Code. Then paste it in.

--
Jim Rech
Excel MVP
"GlenS18" wrote in message
...
|
| I have been sent this little ditty. Apparently it allows you to turn
| cells a certain colour depending on the value entered in it.
| Conditional formatting does not only limits you to 3 choices.
|
| Can anyone explain it to me & how to get started as I have not used the
| VB part of Excel much.
|
| Many thanks.
|
| Private Sub Worksheet_Change(ByVal Target As Range)
| If Not Intersect(Target, Range("B7:U29")) Is Nothing Then
| Select Case Target.Text
| Case Is = "H"
| Target.Interior.ColorIndex = 3
| Case Is = "M"
| Target.Interior.ColorIndex = 45
| Case Is = "L"
| Target.Interior.ColorIndex = 6
| Case Is = "U"
| Target.Interior.ColorIndex = 41
| Case Is = "N"
| Target.Interior.ColorIndex = 50
| Case Else
| Target.Interior.ColorIndex = xlNone
| End Select
| End If
| End Sub
|
|
| --
| GlenS18
| ------------------------------------------------------------------------
| GlenS18's Profile:
http://www.excelforum.com/member.php...fo&userid=9457
| View this thread: http://www.excelforum.com/showthread...hreadid=262706
|