Changing worksheet tab colors
 

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

Changing worksheet tab colors
 

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
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"MarkT" wrote:

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