This calls for VBA Code knowledge I dont have.

Im 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 couldnt get it to work with the example above€¦.I think this paints a
better picture

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

Bob,

Did you get my workbook I e-mailed?

"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

No I didn't.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JVANWORTH" wrote in message
...
Bob,

Did you get my workbook I e-mailed?

"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

I resent it

"Bob Phillips" wrote:

No I didn't.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JVANWORTH" wrote in message
...
Bob,

Did you get my workbook I e-mailed?

"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

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

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

Dave,

I made the change, but still no luck with the color change. I'll keep
plugging away.
Would you mind looking at the workbook I created. I inserted windows to
give the reader an idea what I'm looking for.

"Dave Peterson" wrote:

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

I don't open workbooks from others.

You may want to post your existing code in plain text.

JVANWORTH wrote:

Dave,

I made the change, but still no luck with the color change. I'll keep
plugging away.
Would you mind looking at the workbook I created. I inserted windows to
give the reader an idea what I'm looking for.

"Dave Peterson" wrote:

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


--

Dave Peterson

Dave & Bob,

I added Daves extra line "End Select". Intial links work fine. But the
issue remains the same. When I change a cell in English or Math the linked
cell in Grade 9 will not change color (text changes, no color change)

"Dave Peterson" wrote:

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