I am working on a macro to change tab colors based on two factors but I need to be able to change the tab color to "No Color" if the factors change. I have the code changing the tab colors but I am unable to get it to return to "No Color".
I am working on a macro to change tab colors based on two factors but I need to be able to change the tab color to "No Color" if the factors change. I have the code changing the tab colors but I am unable to get it to return to "No Color".
Macro recorder will usually point you in the right direction
ActiveWorkbook.Sheets("Sheet1").Tab.Color = xlNone
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Thank you your help, How can I keep the same font style as a standard tab?
I didn't think changing the tab color would change the font.
Does it?
Also what do you mean by a 'standard tab'?
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
When I use the macro to set the color to "xlNone" the font color turns to black (see below) the other fonts are blue. If I manually change the tab color to "No Color" the tab font changes to light blue as shown below.
Excel Tabs.jpg
Last edited by oam; 08-14-2015 at 04:11 PM.
See if this is any better. I don't have the blue colors on mine
Capture.JPG
With ActiveWorkbook.Sheets("Sheet1").Tab .ColorIndex = xlAutomatic .TintAndShade = 0 End With
The other thing to try would be to record a macro while you manually reset the color
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
It does not return it to how the tab was before but I may have to change it manually. Do you know how to change the font colors?
Thank you for your help
I think those parameters are part of the Theme - look at the picture and explore with your configuration.
The quickest thing I could suggest is to record a macro to do what you want, and then incorporate the resulting code
Capture.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Thank for your help! I think I have gone as far with this as I can; nowhere in the web info tells how to change the tabs back to there pervious theme so I picked a color that stands out and should work.
Again, thank you for all your help.