Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 50
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can you change the color of one cell based on the color of another andoscott Excel Discussion (Misc queries) 4 May 4th 07 04:02 PM
Excel: Syntax to change cell color based on color of another cell davew18 Excel Worksheet Functions 1 January 4th 07 01:24 PM
change cell color to red & then green when value change in 2 cel Innswood Excel Discussion (Misc queries) 0 July 24th 06 05:07 AM
change background row color with change of date in a cell Urszula Excel Discussion (Misc queries) 5 May 17th 06 07:56 AM
How to change the default Border, Font Color, and Cell Color Elijah Excel Discussion (Misc queries) 3 November 2nd 05 11:52 PM


All times are GMT +1. The time now is 08:47 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright 2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"