Macro to change multiple sheet tabs color based on a cell value

**greywu1f** · 09-10-2012, 05:33 PM

Hey guys,

First time posting. I have been looking for a solution to this problem. I have a spreadsheet that reports back to cell B11 whether a specific conflict is Telephone, Gas, Water, Power, Sanitary, Storm, Cable TV, or Unknown. What I want to do is change the color of the sheet tab based on the value in cell B11. I have tried a few different things but nothing works right. I have a macro to sort the sheets and I would like to add this to it so when a button is pushed it sorts the sheets and colors the tabs. Here's what I have for the sorting macro.

Please Login or Register  to view this content.

I would greatly appreciate any help.

**wallyeye** · 09-10-2012, 07:25 PM

Since you are actually looping through the sheets multiple times, I would recommend putting this in a separate procedure and calling it after you finish sorting:

Please Login or Register  to view this content.

**greywu1f** · 09-10-2012, 09:46 PM

Hey Wallyeye, thanks for replying so quickly. I tried your code didn't work for me. Would it matter that cell B11 is a formula calling data from a cover sheet?

**wallyeye** · 09-11-2012, 03:26 PM

I wouldn't think so, it is specifically retrieving the value in B11. I have seen it fail if the entire cell is not visible, that is you see "Tele" instead of "Telephone" because the column is not wide enough. If this is the case, you can either make the column wider, or truncate the text as:

Please Login or Register  to view this content.

I put the strText in so you can see what is actually being compared, just put a breakpoint on the Select statement (using F9), then run the code. When the line is reached, the IDE will stop and you can hover your mouse over strText to see its current value.

**greywu1f** · 09-18-2012, 02:47 PM

Thanks Chief that did it. It works perfectly. YOU ARE THE MAN!!!

**Cutter** · 09-18-2012, 07:17 PM

@ greywu1f

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at the top of the page and read Rule #3.
Thanks.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.