code for conditional tab color 3

[smiley0q0]

i am a rookie at using code in excel, but i think this is the only way to make happen what i want to happen.

I have a main page where i enter different amounts in column G... about ten of them... then i have 10 seperate sheets, each sheet looks at a single amount... in the individual sheets, they have different allocation tables... so we'll just focus on one sheet for now.
Sheet Z has "51.03" in E1, this is the amount entered from the main sheet. A1 through E37 is the allocation table, basically i have to divide 51.03 by the number of rows in the allocation table... i have set it to round to the 2nd decimal... but i still have to manually go into the table and change some of the rows to either round up or round down, so it matches 51.03 exactly. i do have conditional formatting setup so that i know when it is matched exactly.
now to make a short story long...
What i would like to do is somehow set up the sheets so that if the sum total of the table (E38) doesn't match E1 then turn the color of the tab red. i know i'll have to run a macro to get it to work, but i thought it would be fun to have the code to do that...

any takers?

Thanks,
Smiley

 

[SkipVought]

Hi,

It's really, REALLY hard to write code to make Excel do something that there is no functionality for. Unless you're running 2002, yer up the crik without a paddle.

IF you've got 2002,

turn on your macro recorder,
right click a tab
change a color,
turn off the recorder

Now go see what you got!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smiley0q0]

Thanks Skip,

Don't know why i keep forgetting about recording my macros, i guess i get so caught up in the idea of "coding" i forget that there are things out there that make it easier.

Thanks for the reminder...

and yep, i got it to work... here's the code

Sub Unequal()

Sheets("Sheet Z&quot.Select
If Range(&quot;E1&quot.Value <> Range(&quot;E38&quot.Value Then
ActiveWorkbook.Sheets(&quot;Sheet Z&quot.Tab.ColorIndex = 3
Else
ActiveWorkbook.Sheets(&quot;Sheet Z&quot.Tab.ColorIndex = 15
End If

End Sub

just the basics though... still have to loop it through all my sheets.

thanks again for the reminder. guess i just had a brain fart.

Smiley

 

[SkipVought]

Assuming that ALL sheets get this treatment...

for each ws in worksheets
    If ws.Range(&quot;E1&quot;).Value <> ws.Range(&quot;E38&quot;).Value Then
        ws.Tab.ColorIndex = 3
    Else
        ws.Tab.ColorIndex = 15
    End If
next

(no extra charge! )

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[MeGustaXL]

Nice one Skip, have a STAR!

Unfortunately, I'm using XL2K, so it don't work for me per se, but I've seen a 'hack' somewhere that gives coloured tabs for XL97 upwards. Have another star if you can point us to it!

Chris

Varium et mutabile semper Excel

 

[MeGustaXL]

Found it! It's here:

http://xcelfiles.homestead.com/Excel01_02.html

There is a warning with this hack, because it resets the system colours, but I've played with it and got the effect I desired. JUST USE WITH EXTREME CAUTION.

Caveat Emptor and all that.

Chris

Varium et mutabile semper Excel