MS Excel 2002 : Conditional formating by shading cells
Correction
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "CN": Target.Offset(0, -1).Interior.ColorIndex = 36
Case "RT": Target.Offset(0, -1).Interior.ColorIndex = 38
Case "DJ": Target.Offset(0, -1).Interior.ColorIndex = 37
'etc
End Select
End If
ws_exit:
Application.EnableEvents = True
End Sub
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Bob Phillips" wrote in message
...
You won't get 12 conditions formatted in CF, 4 including default is the
max.
Try this
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "CN": .Offset(0, -1).Interior.ColorIndex = 36
Case "RT": .Offset(0, -1).Interior.ColorIndex = 38
Case "DJ": .Offset(0, -1).Interior.ColorIndex = 37
'etc
End Select
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"Mr. Low" wrote in message
...
Dear Sir,
I have the following table at my workshhet :
A B
1 254 CN
2 321 CN
3 340 CN
4 147 DN
5 321 DN
6 110 DN
7 98 IV
8 654 IV
9 447 RT
10 988 RT
11 228 DJ
12 356 DJ
May I know is there a way to conditional shading the numbers in column A
by
using reference codes in column B ?
Alternatively shading both column A and column B by using the same
reference
code. i.e. figues for CN - light yellow, RT-light red DJ - light blue and
so
on
Also can this be done at a single dialog box for all the different codes
(
10-12) ?
Thanks
Low
--
A36B58K641
|