Colouring Tabs Based on a Cell Value

Hey there, I've been trying to get a macro together which would colour all the tabs in my workbook based on whether or not a certain value exists in a certain cell. In this case the cell is C7 and the word there is "elective class:".

At the moment this is what I have come up with:

Please Login or Register  to view this content.

However it doesn't work, it colours everything as though "elective class:" exists on none of the sheets. How can I correct this?

Also I am actually going to go further with this code and will want all the sheets of one colour next to each other and all the sheets of the other colour next to each other. Excluding however the first 6 sheets which should be ignored by the macro. If anyone can help with this at the same time it would be appreciated as I have no clue how to do this part.

Many thanks,

Tom

P.S. Personally if I remember correctly I think it might be something to do with using the mySht.Range("C7").Value part of the macro. I seem to remember having a similar problem with text before. I tried changing the word Value to the word Text but to no avail.

Ah, for shame.

Thanks for that.


How can I get it to sort only the 7th worksheet onwards by the way?

One way might be with a counter:

Please Login or Register  to view this content.

or you could access your sheets via the index instead of 'For Each...'

Please Login or Register  to view this content.

The second method should be quicker, and certainly would be if you are starting at a higher numbered sheet, as it will not cycle through all the sheets that you AREN'T interested in, whereas the first method would, (whilst incrementing it's counter)

Ooops, sorry about that. My bad as I didn't even look, I normally just use "Next" so it wouldn't have been a problem :P