Expert VBE help for cell color change

I need VBE/code help for changing color in a worksheet if a condition is met.

Im working on a spreadsheet for the Master Schedule at the high school
where I work. I have created 10 worksheets that help us track # of teacher,
# of students per class, # per level, and course info, etc€¦..so I have
separate sheets for English, math, science & so forth€¦€¦€¦

I have three worksheets (Grade Level, Academic Level, and Student Level)
that are linked to all ten and reflect all the info that is in all 10 sheets.

Here is where I need help. In the three linked worksheets (Grade Level,
Academic Level & Student Level) I need linked cells with certain class info
to change color.

For Example:
In the €œEnglish€� worksheet I have drop down menus to select courses for
Teachers listed down the left side of the sheet (same goes for all subjects).
In worksheet €œGrade Level€� I have linked J7 to J7 in €œEnglish€� (because the
formatting matches). If I picked €˜English 9 General in the €œEnglish€� sheet,
I want the link cell, J7, in the €œGrade Level€� sheet to be blue. If I pick
€˜English 10 Gate in the €œEnglish€� sheet, I want the link cell in €œGrade
Level€� sheet to be green. (I have the code number for color). Same would go
for the €œAcademic Level€� & €œStudent Level€�.

I have had help in the past with something similar but I am too inept to
adapt this code to my worksheet. Here is the code that I cannot seem to
adapt to my spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) _
Is Nothing Then Exit Sub
Select Case UCase(Target.Value)
Case "ENG 9 GEN", "MATH 9", "SCI 9"
icolor = 3
Case "ENG 10", "MATH 10", "SCI 10"
icolor = 4
Case "ENG 11", "MATH 11", "SCI 11"
icolor = 5
Case "ENG 12", "MATH 12", "SCI 12"
icolor = 6
Case Else
End Select
Target.Interior.ColorIndex = icolor
For i = 1 To 3
With Worksheets("Sheet" & i)
For Each cell In .Range("A1: G10 ")
If cell.Value = Target.Value Then
cell.Interior.ColorIndex = icolor
End If
Next cell
End With
Next i

End Sub

I would be willing to send the worksheet if that would help.

Thanks in advance,

John

John,

That looks quite complex, when laid out in words.

Can you post me the workbook? See my signature to see how to adapt my email
address.

--
HTH

Bob

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

"JVANWORTH" wrote in message
...
Expert VBE help for cell color change

I need VBE/code help for changing color in a worksheet if a condition is
met.

I'm working on a spreadsheet for the Master Schedule at the high school
where I work. I have created 10 worksheets that help us track # of
teacher,
# of students per class, # per level, and course info, etc...so I have
separate sheets for English, math, science & so forth...

I have three worksheets (Grade Level, Academic Level, and Student Level)
that are linked to all ten and reflect all the info that is in all 10
sheets.

Here is where I need help. In the three linked worksheets (Grade Level,
Academic Level & Student Level) I need linked cells with certain class
info
to change color.

For Example:
In the "English" worksheet I have drop down menu's to select courses for
Teachers listed down the left side of the sheet (same goes for all
subjects).
In worksheet "Grade Level" I have linked J7 to J7 in "English" (because
the
formatting matches). If I picked 'English 9 General' in the "English"
sheet,
I want the link cell, J7, in the "Grade Level" sheet to be blue. If I
pick
'English 10 Gate' in the "English" sheet, I want the link cell in "Grade
Level" sheet to be green. (I have the code number for color). Same would
go
for the "Academic Level" & "Student Level".

I have had help in the past with something similar but I am too inept to
adapt this code to my worksheet. Here is the code that I cannot seem to
adapt to my spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) _
Is Nothing Then Exit Sub
Select Case UCase(Target.Value)
Case "ENG 9 GEN", "MATH 9", "SCI 9"
icolor = 3
Case "ENG 10", "MATH 10", "SCI 10"
icolor = 4
Case "ENG 11", "MATH 11", "SCI 11"
icolor = 5
Case "ENG 12", "MATH 12", "SCI 12"
icolor = 6
Case Else
End Select
Target.Interior.ColorIndex = icolor
For i = 1 To 3
With Worksheets("Sheet" & i)
For Each cell In .Range("A1: G10 ")
If cell.Value = Target.Value Then
cell.Interior.ColorIndex = icolor
End If
Next cell
End With
Next i

End Sub

I would be willing to send the worksheet if that would help.

Thanks in advance,

John

Bob,

Did you get the workbook I e-mailed to you?

"Bob Phillips" wrote:

John,

That looks quite complex, when laid out in words.

Can you post me the workbook? See my signature to see how to adapt my email
address.

--
HTH

Bob

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

"JVANWORTH" wrote in message
...
Expert VBE help for cell color change

I need VBE/code help for changing color in a worksheet if a condition is
met.

I'm working on a spreadsheet for the Master Schedule at the high school
where I work. I have created 10 worksheets that help us track # of
teacher,
# of students per class, # per level, and course info, etc...so I have
separate sheets for English, math, science & so forth...

I have three worksheets (Grade Level, Academic Level, and Student Level)
that are linked to all ten and reflect all the info that is in all 10
sheets.

Here is where I need help. In the three linked worksheets (Grade Level,
Academic Level & Student Level) I need linked cells with certain class
info
to change color.

For Example:
In the "English" worksheet I have drop down menu's to select courses for
Teachers listed down the left side of the sheet (same goes for all
subjects).
In worksheet "Grade Level" I have linked J7 to J7 in "English" (because
the
formatting matches). If I picked 'English 9 General' in the "English"
sheet,
I want the link cell, J7, in the "Grade Level" sheet to be blue. If I
pick
'English 10 Gate' in the "English" sheet, I want the link cell in "Grade
Level" sheet to be green. (I have the code number for color). Same would
go
for the "Academic Level" & "Student Level".

I have had help in the past with something similar but I am too inept to
adapt this code to my worksheet. Here is the code that I cannot seem to
adapt to my spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) _
Is Nothing Then Exit Sub
Select Case UCase(Target.Value)
Case "ENG 9 GEN", "MATH 9", "SCI 9"
icolor = 3
Case "ENG 10", "MATH 10", "SCI 10"
icolor = 4
Case "ENG 11", "MATH 11", "SCI 11"
icolor = 5
Case "ENG 12", "MATH 12", "SCI 12"
icolor = 6
Case Else
End Select
Target.Interior.ColorIndex = icolor
For i = 1 To 3
With Worksheets("Sheet" & i)
For Each cell In .Range("A1: G10 ")
If cell.Value = Target.Value Then
cell.Interior.ColorIndex = icolor
End If
Next cell
End With
Next i

End Sub

I would be willing to send the worksheet if that would help.

Thanks in advance,

John

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 the workbook I e-mailed to you?

"Bob Phillips" wrote:

John,

That looks quite complex, when laid out in words.

Can you post me the workbook? See my signature to see how to adapt my
email
address.

--
HTH

Bob

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

"JVANWORTH" wrote in message
...
Expert VBE help for cell color change

I need VBE/code help for changing color in a worksheet if a condition
is
met.

I'm working on a spreadsheet for the Master Schedule at the high school
where I work. I have created 10 worksheets that help us track # of
teacher,
# of students per class, # per level, and course info, etc...so I have
separate sheets for English, math, science & so forth...

I have three worksheets (Grade Level, Academic Level, and Student
Level)
that are linked to all ten and reflect all the info that is in all 10
sheets.

Here is where I need help. In the three linked worksheets (Grade
Level,
Academic Level & Student Level) I need linked cells with certain class
info
to change color.

For Example:
In the "English" worksheet I have drop down menu's to select courses
for
Teachers listed down the left side of the sheet (same goes for all
subjects).
In worksheet "Grade Level" I have linked J7 to J7 in "English" (because
the
formatting matches). If I picked 'English 9 General' in the "English"
sheet,
I want the link cell, J7, in the "Grade Level" sheet to be blue. If I
pick
'English 10 Gate' in the "English" sheet, I want the link cell in
"Grade
Level" sheet to be green. (I have the code number for color). Same
would
go
for the "Academic Level" & "Student Level".

I have had help in the past with something similar but I am too inept
to
adapt this code to my worksheet. Here is the code that I cannot seem
to
adapt to my spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) _
Is Nothing Then Exit Sub
Select Case UCase(Target.Value)
Case "ENG 9 GEN", "MATH 9", "SCI 9"
icolor = 3
Case "ENG 10", "MATH 10", "SCI 10"
icolor = 4
Case "ENG 11", "MATH 11", "SCI 11"
icolor = 5
Case "ENG 12", "MATH 12", "SCI 12"
icolor = 6
Case Else
End Select
Target.Interior.ColorIndex = icolor
For i = 1 To 3
With Worksheets("Sheet" & i)
For Each cell In .Range("A1: G10 ")
If cell.Value = Target.Value Then
cell.Interior.ColorIndex = icolor
End If
Next cell
End With
Next i

End Sub

I would be willing to send the worksheet if that would help.

Thanks in advance,

John

I tried to send it to the following address:


under


"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 the workbook I e-mailed to you?

"Bob Phillips" wrote:

John,

That looks quite complex, when laid out in words.

Can you post me the workbook? See my signature to see how to adapt my
email
address.

--
HTH

Bob

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

"JVANWORTH" wrote in message
...
Expert VBE help for cell color change

I need VBE/code help for changing color in a worksheet if a condition
is
met.

I'm working on a spreadsheet for the Master Schedule at the high school
where I work. I have created 10 worksheets that help us track # of
teacher,
# of students per class, # per level, and course info, etc...so I have
separate sheets for English, math, science & so forth...

I have three worksheets (Grade Level, Academic Level, and Student
Level)
that are linked to all ten and reflect all the info that is in all 10
sheets.

Here is where I need help. In the three linked worksheets (Grade
Level,
Academic Level & Student Level) I need linked cells with certain class
info
to change color.

For Example:
In the "English" worksheet I have drop down menu's to select courses
for
Teachers listed down the left side of the sheet (same goes for all
subjects).
In worksheet "Grade Level" I have linked J7 to J7 in "English" (because
the
formatting matches). If I picked 'English 9 General' in the "English"
sheet,
I want the link cell, J7, in the "Grade Level" sheet to be blue. If I
pick
'English 10 Gate' in the "English" sheet, I want the link cell in
"Grade
Level" sheet to be green. (I have the code number for color). Same
would
go
for the "Academic Level" & "Student Level".

I have had help in the past with something similar but I am too inept
to
adapt this code to my worksheet. Here is the code that I cannot seem
to
adapt to my spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) _
Is Nothing Then Exit Sub
Select Case UCase(Target.Value)
Case "ENG 9 GEN", "MATH 9", "SCI 9"
icolor = 3
Case "ENG 10", "MATH 10", "SCI 10"
icolor = 4
Case "ENG 11", "MATH 11", "SCI 11"
icolor = 5
Case "ENG 12", "MATH 12", "SCI 12"
icolor = 6
Case Else
End Select
Target.Interior.ColorIndex = icolor
For i = 1 To 3
With Worksheets("Sheet" & i)
For Each cell In .Range("A1: G10 ")
If cell.Value = Target.Value Then
cell.Interior.ColorIndex = icolor
End If
Next cell
End With
Next i

End Sub

I would be willing to send the worksheet if that would help.

Thanks in advance,

John

First, the reason that Bob munges his address is that he doesn't want those
newsreading bots to scan the messages and extract his email address.

If you had posted his real address, then Bob's efforts would have been wasted.

Second, you missed his instructions:

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

so it's:


You may want to munge your email address in future posts, too. I include XSpam
in mine to try to keep the spam levels down.

Lots of people use things like:
jvanworth(At)DeletethisMSN.COM





JVANWORTH wrote:

I tried to send it to the following address:


under

"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 the workbook I e-mailed to you?

"Bob Phillips" wrote:

John,

That looks quite complex, when laid out in words.

Can you post me the workbook? See my signature to see how to adapt my
email
address.

--
HTH

Bob

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

"JVANWORTH" wrote in message
...
Expert VBE help for cell color change

I need VBE/code help for changing color in a worksheet if a condition
is
met.

I'm working on a spreadsheet for the Master Schedule at the high school
where I work. I have created 10 worksheets that help us track # of
teacher,
# of students per class, # per level, and course info, etc...so I have
separate sheets for English, math, science & so forth...

I have three worksheets (Grade Level, Academic Level, and Student
Level)
that are linked to all ten and reflect all the info that is in all 10
sheets.

Here is where I need help. In the three linked worksheets (Grade
Level,
Academic Level & Student Level) I need linked cells with certain class
info
to change color.

For Example:
In the "English" worksheet I have drop down menu's to select courses
for
Teachers listed down the left side of the sheet (same goes for all
subjects).
In worksheet "Grade Level" I have linked J7 to J7 in "English" (because
the
formatting matches). If I picked 'English 9 General' in the "English"
sheet,
I want the link cell, J7, in the "Grade Level" sheet to be blue. If I
pick
'English 10 Gate' in the "English" sheet, I want the link cell in
"Grade
Level" sheet to be green. (I have the code number for color). Same
would
go
for the "Academic Level" & "Student Level".

I have had help in the past with something similar but I am too inept
to
adapt this code to my worksheet. Here is the code that I cannot seem
to
adapt to my spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) _
Is Nothing Then Exit Sub
Select Case UCase(Target.Value)
Case "ENG 9 GEN", "MATH 9", "SCI 9"
icolor = 3
Case "ENG 10", "MATH 10", "SCI 10"
icolor = 4
Case "ENG 11", "MATH 11", "SCI 11"
icolor = 5
Case "ENG 12", "MATH 12", "SCI 12"
icolor = 6
Case Else
End Select
Target.Interior.ColorIndex = icolor
For i = 1 To 3
With Worksheets("Sheet" & i)
For Each cell In .Range("A1: G10 ")
If cell.Value = Target.Value Then
cell.Interior.ColorIndex = icolor
End If
Next cell
End With
Next i

End Sub

I would be willing to send the worksheet if that would help.

Thanks in advance,

John








--

Dave Peterson

Dave,
got it.............my mom always said I was sharp as a marble

"Dave Peterson" wrote:

First, the reason that Bob munges his address is that he doesn't want those
newsreading bots to scan the messages and extract his email address.

If you had posted his real address, then Bob's efforts would have been wasted.

Second, you missed his instructions:

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

so it's:


You may want to munge your email address in future posts, too. I include XSpam
in mine to try to keep the spam levels down.

Lots of people use things like:
jvanworth(At)DeletethisMSN.COM





JVANWORTH wrote:

I tried to send it to the following address:


under

"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 the workbook I e-mailed to you?

"Bob Phillips" wrote:

John,

That looks quite complex, when laid out in words.

Can you post me the workbook? See my signature to see how to adapt my
email
address.

--
HTH

Bob

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

"JVANWORTH" wrote in message
...
Expert VBE help for cell color change

I need VBE/code help for changing color in a worksheet if a condition
is
met.

I'm working on a spreadsheet for the Master Schedule at the high school
where I work. I have created 10 worksheets that help us track # of
teacher,
# of students per class, # per level, and course info, etc...so I have
separate sheets for English, math, science & so forth...

I have three worksheets (Grade Level, Academic Level, and Student
Level)
that are linked to all ten and reflect all the info that is in all 10
sheets.

Here is where I need help. In the three linked worksheets (Grade
Level,
Academic Level & Student Level) I need linked cells with certain class
info
to change color.

For Example:
In the "English" worksheet I have drop down menu's to select courses
for
Teachers listed down the left side of the sheet (same goes for all
subjects).
In worksheet "Grade Level" I have linked J7 to J7 in "English" (because
the
formatting matches). If I picked 'English 9 General' in the "English"
sheet,
I want the link cell, J7, in the "Grade Level" sheet to be blue. If I
pick
'English 10 Gate' in the "English" sheet, I want the link cell in
"Grade
Level" sheet to be green. (I have the code number for color). Same
would
go
for the "Academic Level" & "Student Level".

I have had help in the past with something similar but I am too inept
to
adapt this code to my worksheet. Here is the code that I cannot seem
to
adapt to my spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) _
Is Nothing Then Exit Sub
Select Case UCase(Target.Value)
Case "ENG 9 GEN", "MATH 9", "SCI 9"
icolor = 3
Case "ENG 10", "MATH 10", "SCI 10"
icolor = 4
Case "ENG 11", "MATH 11", "SCI 11"
icolor = 5
Case "ENG 12", "MATH 12", "SCI 12"
icolor = 6
Case Else
End Select
Target.Interior.ColorIndex = icolor
For i = 1 To 3
With Worksheets("Sheet" & i)
For Each cell In .Range("A1: G10 ")
If cell.Value = Target.Value Then
cell.Interior.ColorIndex = icolor
End If
Next cell
End With
Next i

End Sub

I would be willing to send the worksheet if that would help.

Thanks in advance,

John








--

Dave Peterson

But you must have been pretty bright.

She called you son!

JVANWORTH wrote:

Dave,
got it.............my mom always said I was sharp as a marble

<<snipped

I tried again, with a little help from my friends

"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 the workbook I e-mailed to you?

"Bob Phillips" wrote:

John,

That looks quite complex, when laid out in words.

Can you post me the workbook? See my signature to see how to adapt my
email
address.

--
HTH

Bob

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

"JVANWORTH" wrote in message
...
Expert VBE help for cell color change

I need VBE/code help for changing color in a worksheet if a condition
is
met.

I'm working on a spreadsheet for the Master Schedule at the high school
where I work. I have created 10 worksheets that help us track # of
teacher,
# of students per class, # per level, and course info, etc...so I have
separate sheets for English, math, science & so forth...

I have three worksheets (Grade Level, Academic Level, and Student
Level)
that are linked to all ten and reflect all the info that is in all 10
sheets.

Here is where I need help. In the three linked worksheets (Grade
Level,
Academic Level & Student Level) I need linked cells with certain class
info
to change color.

For Example:
In the "English" worksheet I have drop down menu's to select courses
for
Teachers listed down the left side of the sheet (same goes for all
subjects).
In worksheet "Grade Level" I have linked J7 to J7 in "English" (because
the
formatting matches). If I picked 'English 9 General' in the "English"
sheet,
I want the link cell, J7, in the "Grade Level" sheet to be blue. If I
pick
'English 10 Gate' in the "English" sheet, I want the link cell in
"Grade
Level" sheet to be green. (I have the code number for color). Same
would
go
for the "Academic Level" & "Student Level".

I have had help in the past with something similar but I am too inept
to
adapt this code to my worksheet. Here is the code that I cannot seem
to
adapt to my spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:G10")) _
Is Nothing Then Exit Sub
Select Case UCase(Target.Value)
Case "ENG 9 GEN", "MATH 9", "SCI 9"
icolor = 3
Case "ENG 10", "MATH 10", "SCI 10"
icolor = 4
Case "ENG 11", "MATH 11", "SCI 11"
icolor = 5
Case "ENG 12", "MATH 12", "SCI 12"
icolor = 6
Case Else
End Select
Target.Interior.ColorIndex = icolor
For i = 1 To 3
With Worksheets("Sheet" & i)
For Each cell In .Range("A1: G10 ")
If cell.Value = Target.Value Then
cell.Interior.ColorIndex = icolor
End If
Next cell
End With
Next i

End Sub

I would be willing to send the worksheet if that would help.

Thanks in advance,

John