ExcelBanter - View Single Post

JVANWORTH

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