Remember that you need an "End Select" statement, too:

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
With Target
.Interior.ColorIndex = xlColorIndexNone

Select Case .Value

Case "Geometry": .Interior.ColorIndex = 35 'light green
Case "CAHSEE Math": .Interior.ColorIndex = 7 'pink
Case "9th Lit": .Interior.ColorIndex = 10 'green
Case "S-Cap": .Interior.ColorIndex = 36 'light yellow
'etc.

End Select '<-- added

End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

JVANWORTH wrote:

Bob,

I'm getting hung up on the line"

End with

in the code that you sent me

"Bob Phillips" wrote:

Add this to the Grade 9 worksheet

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
With Target
.Interior.ColorIndex = xlColorIndexNone

Select Case .Value

Case "Geometry": .Interior.ColorIndex = 35 'light green
Case "CAHSEE Math": .Interior.ColorIndex = 7 'pink
Case "9th Lit": .Interior.ColorIndex = 10 'green
Case "S-Cap": .Interior.ColorIndex = 36 'light yellow
'etc.
End With
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)

"JVANWORTH" wrote in message
...
This calls for VBA Code knowledge I don't have.

I'm revisiting an old High School Scheduling project with a clearer
objective.

Microsoft Office EXCEL 2003

How do I get the cells that are linked to another worksheet to change
colors
if a condition is met. I will need about four colors to correspond with
approximately 15 plus conditions.

Streamlined Example Follows:

In the workbook I have two (2) worksheets named: English & Math (I will be
adding more Wrkshts as demand grows). Each has a pull down menu to assign
teachers classes for the semester.

Example of English Wrksht:
A B
1 Bicks English Art (column B selected from pull down menu)
2 Jotos 9th Lit
3 Pordan S-CAP

Math Wrksht:
A B
1 Adleman Algebra (column B selected from pull down menu)
2 Fuller Geometry
3 Johnson CAHSEE Math

And so on

In another Wrksht called Grade 9 I link up to combine English and Math to
present data in a format with color.
A B C
1 ENG Bicks English Art (cell needs blue) - (B1 & C1
link to English)
2 MATH Fuller Geometry (cell needs light green)
3 MATH Johnson CAHSEE Math (cell needs pink)
4 ENG Jotos 9th Lit (cell in light green)
5 ENG Pordon S-Cap (cell in light yellow)

As I manipulate English and Math wrkshts and change courses I need Grade 9
Wrksht to change colors. Wrkshts English and Math will not have color.


PS..Thanks to Bob, Rick "MVP", and Toppers for your past help with code.
I
just couldn't get it to work with the example above..I think this paints
a
better picture






--

Dave Peterson