MS Excel 2002 : Conditional formating by shading cells
 

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

MS Excel 2002 : Conditional formating by shading cells
 

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

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