Hi I have a workbook which has some 22 tabs. The first worksheet is called
"Cover Page" and it has a list of all the other tabs (tab names are not
constant) in range A10:A30. The users can change the colors of individual
cells in range A10:A30. What I would like to do is change the color of the
tab to match the color of its corresponding cell in range A10:A30 on "Cover
Page"

Excel version is 2003, can anyone help me out?

Hi

Give this a shot:


Private Sub Worksheet_Deactivate()

Dim i As Integer

For i = 1 To 21

ThisWorkbook.Worksheets(i + 1).Tab.ColorIndex =
Sheets(1).Range("A10:A30").Cells(i, 1).Interior.ColorIndex

Next

End Sub


You put the code in the code for the Cover page, *not* somewhere else (for
instance in a module).

Whenever you leave the Cover page sheet, the colors of the sheets will be
updated.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Scott" wrote:

Hi I have a workbook which has some 22 tabs. The first worksheet is called
"Cover Page" and it has a list of all the other tabs (tab names are not
constant) in range A10:A30. The users can change the colors of individual
cells in range A10:A30. What I would like to do is change the color of the
tab to match the color of its corresponding cell in range A10:A30 on "Cover
Page"

Excel version is 2003, can anyone help me out?

Worked a treat, ta very much!

"Wigi" wrote:

Hi

Give this a shot:


Private Sub Worksheet_Deactivate()

Dim i As Integer

For i = 1 To 21

ThisWorkbook.Worksheets(i + 1).Tab.ColorIndex =
Sheets(1).Range("A10:A30").Cells(i, 1).Interior.ColorIndex

Next

End Sub


You put the code in the code for the Cover page, *not* somewhere else (for
instance in a module).

Whenever you leave the Cover page sheet, the colors of the sheets will be
updated.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Scott" wrote:

Hi I have a workbook which has some 22 tabs. The first worksheet is called
"Cover Page" and it has a list of all the other tabs (tab names are not
constant) in range A10:A30. The users can change the colors of individual
cells in range A10:A30. What I would like to do is change the color of the
tab to match the color of its corresponding cell in range A10:A30 on "Cover
Page"

Excel version is 2003, can anyone help me out?