Expert VBE help for cell color change

[Guest]

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 Phillips]

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)

 

[Guest]

Bob,

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

 

[Bob Phillips]

No I didn't

--
HTH

Bob

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

 

[Guest]

I tried to send it to the following address:
(e-mail address removed)

under (e-mail address removed)

 

[Dave Peterson]

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:
(e-mail address removed)

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

 

[Guest]

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

 

[Dave Peterson]

But you must have been pretty bright.

She called you son!

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

<<snipped>>

 

[Guest]

I tried again, with a little help from my friends