Expert VBE help for cell color change

J

JVANWORTH

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
 
B

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)
 
B

Bob Phillips

No I didn't

--
HTH

Bob

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

JVANWORTH

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

under (e-mail address removed)
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top