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
|