VBA Help

  • Thread starter Thread starter GlenS18
  • Start date Start date
G

GlenS18

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
 
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
 
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
|
| 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.php?threadid=262706
|
 
Back
Top