Tab Formatting - conditional

[mjwillyone]

I am creating an Excel spreadsheet that will be distributed to othe
staff members. The spreadsheet is basically a multi-page (tab) monthl
expense report. While each tab represents a particular form (busines
expenses, mileage logs, etc), not all of them will be filled in by th
employee. When we receive the completed reports, they are converted t
Adobe Acrobat format and saved to our office LAN environment.

I would like to fiind a way to change the text color of each tab nam
based upon the presence of a value greater than zero on its page. Fo
instance, if the employee does not complete the mileage log found o
tab 2, the tab text "Mileage Log" would remain black. However, if th
employee does complete the log, then there would be a mileage total o
the sheet. Because the total is greater than zero, I would like th
tab text to be colored Red.

The secretary who converts the Excel pages to Adobe Acrobat woul
simply select only the red-colored tabs to convert.

Can anyone help?

Thanks,
Mik

 

[Don Guillett]

try

Sub ColorWorksheetTabs()
For Each ws In Sheets
If ws.Range("a34") > 0 Then
ws.Tab.ColorIndex = 46
Else
ws.Tab.ColorIndex = -4142
End If
End Sub

 

[mjwillyone]

Don,

This looks like code to me! Where do I enter this code in order for it
to work?

Thanks,
Mike

 

[Don Guillett]

The easiest way for you is to right click on the sheet tab>view
code>copy/paste>SAVE workbook.
You can execute from alt f8 or assign to a button or shape.