GDay,

I have a worksheet which users can change the color of a particular cell €“
assume C3. What I want to do is change the color of another worksheet tab
(within the same workbook) to match the cell color of C3.

Can anyone help?

Cheers

Scott

hi, Scott !

I have a worksheet which users can change the color of a particular cell assume C3.
What I want to do is change the color of another worksheet tab (within the same workbook) to match the cell color of C3.

assuming the "other" worksheet is "Sheet2", put this code into C3 cell's source sheet (code module)...
(note you will need '_selectionchange' event to be triggered and excel version 2002/xp or above)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColor As Integer
myColor = Range("c3").Interior.ColorIndex
With Worksheets("sheet2").Tab
If .ColorIndex < myColor Then .ColorIndex = myColor
End With
End Sub

hth,
hector.

Hector,

Thanks for the response. I tried this but unfortunately i get the error
"run-time error 438 Object doesn't support this property or method" and then
the VB Editor highlights the code line:
With Worksheets("sheet2").Tab

I tried renaming the sheet "Sheet2" in case the capital made a difference
and tried different sheets, but all with no luck.

I am running Excel 2000.

Appreciate your help

Scott

"Héctor Miguel" wrote:

hi, Scott !

I have a worksheet which users can change the color of a particular cell assume C3.
What I want to do is change the color of another worksheet tab (within the same workbook) to match the cell color of C3.

assuming the "other" worksheet is "Sheet2", put this code into C3 cell's source sheet (code module)...
(note you will need '_selectionchange' event to be triggered and excel version 2002/xp or above)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColor As Integer
myColor = Range("c3").Interior.ColorIndex
With Worksheets("sheet2").Tab
If .ColorIndex < myColor Then .ColorIndex = myColor
End With
End Sub

hth,
hector.

The ability to change tab colors was added in xl2002.

Scott wrote:

Hector,

Thanks for the response. I tried this but unfortunately i get the error
"run-time error 438 Object doesn't support this property or method" and then
the VB Editor highlights the code line:
With Worksheets("sheet2").Tab

I tried renaming the sheet "Sheet2" in case the capital made a difference
and tried different sheets, but all with no luck.

I am running Excel 2000.

Appreciate your help

Scott

"Héctor Miguel" wrote:

hi, Scott !

I have a worksheet which users can change the color of a particular cell assume C3.
What I want to do is change the color of another worksheet tab (within the same workbook) to match the cell color of C3.

assuming the "other" worksheet is "Sheet2", put this code into C3 cell's source sheet (code module)...
(note you will need '_selectionchange' event to be triggered and excel version 2002/xp or above)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myColor As Integer
myColor = Range("c3").Interior.ColorIndex
With Worksheets("sheet2").Tab
If .ColorIndex < myColor Then .ColorIndex = myColor
End With
End Sub

hth,
hector.




--

Dave Peterson

hi, Scott !

... i get the error "run-time error 438 Object doesn't support this property or method"
and then the VB Editor highlights the code line:
With Worksheets("sheet2").Tab
(...)
I am running Excel 2000.

I think you missed this part from my proposal...

(note you will need '_selectionchange' event to be triggered and excel version 2002/xp or above)

(I'm sorry but xl-2000 doesn't support tab color changes)

regards,
hector.

Hectoe,

Thx for your time, I am sorry I miss understood - I thought I needed to a
selectchange event IF it was excel 2002+ not AND.

Again thanks fro your time

Scott

"Héctor Miguel" wrote:

hi, Scott !

... i get the error "run-time error 438 Object doesn't support this property or method"
and then the VB Editor highlights the code line:
With Worksheets("sheet2").Tab
(...)
I am running Excel 2000.

I think you missed this part from my proposal...

(note you will need '_selectionchange' event to be triggered and excel version 2002/xp or above)

(I'm sorry but xl-2000 doesn't support tab color changes)

regards,
hector.