Changing Multiple Tab Colors based on lookup value VBA?????

[r]

I am very new to VBA and am looking for help with the following
problem:

My workbook contains ten tabs that link back to a central sheet. I
would like the color of each tab to change (Green vs No Color) based
on whether a certain cell in each tab meets a certain requirement.

Example: If cell (V1) in tabs 1, 2 & 5 = "Yes" then color the tab
green else no color. Note, (V1) is a lookup function that gives a
value from the central sheet. The remaining tabs (3, 6,7,8,9 & 10)
would have no color since the lookup function is not equal to "Yes".


Note, the central tab uses pull down data vaildation in which the user
either chooses "Yes" or "".


Any help would be greatly appreciated. Thanks!

 

[Bernie Deitrick]

r,

Here's a macro that will work.

HTH,
Bernie
MS Excel MVP

Sub ColorTabsMacro()
Dim mySht As Worksheet
For Each mySht In ActiveWorkbook.Worksheets
If UCase(mySht.Range("V1").Value) = "YES" Then
mySht.Tab.ColorIndex = 50
Else
mySht.Tab.ColorIndex = xlNone
End If
Next mySht
End Sub

 

[r]

Bear with me on this one.....does this code need to be copied to each
tab? I copied it to my work book and it does not seem to work (yet).
Does "mySht" have to be the name of my individual tabs?

 

[Bernie Deitrick]

r,

The code needs to go into a codemodule. Visit here for an introduction:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Specifically, the part "Use someone else's macro (#havemacro)"

The macro doesn't care what the worksheets' names are - mySht is a worksheet object variable (the
Dim mySht As Worksheet part) that iterates through the collection of all worksheets (the For Each
mySht In ActiveWorkbook.Worksheets part).

HTH,
Bernie
MS Excel MVP

 

[r]

Worked perfectly. Thanks a bunch!

Ready for my next issue????



I have an if formula that yeilds three different symbols based on the
selection of another cell. Works perfectly on my computer, but the
symbols show up as squares (in both the formula bar and on screen) on
all other users computers. I thought that maybe they did not have the
ttf installed, but theirs appear to be the same as mine?

 

[Bernie Deitrick]

r,

No idea on this one - you should post this in a new thread, because most people won't look here for
a new problem.

HTH,
Bernie
MS Excel MVP