Expert VBE help for cell color change
 

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