VBA Code; need linked cells to change color if condition met

[JVANWORTH]

Dave,

"Source" is where I make the manual changes, however, I do not want color or
color changes in "Source". I only want color and color changes to happen in
"Sheet1, 2, 3,.......

Thanks for all your help,

John

 

[Dave Peterson]

The code still goes behind Source. That's where the changes are being made.

But this is the line that changes your target (changed cell):
Target.Interior.ColorIndex = iColor
Delete it or comment it:
'Target.Interior.ColorIndex = iColor

 

[Dave Peterson]

I should have written:
The code still goes behind Source. That's where you're typing the changes.

 

[JVANWORTH]

Dave,

That was the ticket. I got your code to work with multiple sheets. It even
works with incorporated drop down list

Now I'm going to attempt to apply the code to my existing Master Schedule
project. It's much larger in scope, but I believe your help has finally got
the color change worked out.

I will let you know how things work out as soon as possible.

I can't think you enough for your continued support and patience.

Sincerely,
John

 

[Dave Peterson]

It was a struggle, but glad you got it working <bg>.

 

[JVANWORTH]

Things are proceeding but slowly. Couple of questions.

I have a completed workbook that I manipulated as follows:

I have ten (10) worksheets that I use as sources:
English
Social Stud
Math
Science
Wrld Lang
PE Health
Special Ed
VPA
Career Ed
Misc

Sheet1, Sheet2, Sheet3 and Sheet4 are linked to all ten worksheets.

I pasted your modified code for source sheets into each of the ten (10)
source sheets (I added courses to the color sectiom)


Then I pasted your original code into sheets1234.

At first no cells changed colors. Then I clicked on a linked cell, hit
<enter> and all the ‘ENG 9 GEN’’ cells changed red………..as I did this with
each course. They would change to their designated color. My computer take
34 seconds to grind thru each change, but it works. When I change the
source…color changes….very nice.

Do I need to go thru and click/<enter> all the cells?

Other question:

We have three (3) different level of classes: General, College Prep and Gate
for each grade level: 9th, 10th, 11th, 12th.

Can I tweak Sheet1 so it only changes color for Freshmen General-red,
College Prep-blue and Gate-yellow classes, and so on…….. Sheet2 for
Sophomore, Sheet3 for juniors and Sheet4 for seniors?

Right now I have Sheets234 changing the same colors for all grade all
levels……..which it should do with your code

 

[Dave Peterson]

The code you have only runs if you make a change to the worksheet.

You could remove that requirement and make the code run on demand (maybe a
button to start it--maybe tools|macros|macro). But then it wouldn't be
automatic when you changed the data.

And you'd have to add some checks to make sure you're on the correct type of
data before you make the change.