Changing worksheet tab colors

[MarkT]

Greetings all!

I have a spreadsheet created in Excel 2007 that I am looking to have the tab
of each sheet the same color as a specific cell within that sheet. The cell
(M20) is a results cell that contains a short formula and has conditional
formatting which will shade the cell green if positive or red if negative. I
would like each tab for each sheet to either be green or red depending on the
color of cell M20.

Is this possible? If not, can I have a check-box so that if selected the
tab for the sheet will turn a specific color?

Thanks in advance.

Mark

 

[John Bundy]

in the workbook module you can put this, the example checks each tab any time
one is clicked and sets its colorindex to the value in M20. Colorindex
requires a number but you can play with it to get it how you want it.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
For i = 1 To Sheets.Count
Sheets(i).Tab.ColorIndex = Cells(13, 20)

Next
End Sub

Or to go off of the value you can hard code something like

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
For i = 1 To Sheets.Count
if Sheets(i).Cells(13, 20)<=0 then Sheets(i).Tab.ColorIndex = 3
if Sheets(i).Cells(13, 20)>0 then Sheets(i).Tab.ColorIndex = 4
Next
End Sub

change the number to change the color