Find a color value   

G’Day,

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

date: Tue, 8 Jul 2008 17:04:01 -0700   author:   Scott

Re: Find a color value   

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.

date: Tue, 8 Jul 2008 23:26:24 -0500   author:   H?ctor Miguel

Re: Find a color value   

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. 
> 
> 
>

date: Wed, 9 Jul 2008 14:40:24 -0700   author:   Scott

Re: Find a color value   

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

date: Wed, 09 Jul 2008 16:50:02 -0500   author:   Dave Peterson

Re: Find a color value   

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.

date: Wed, 9 Jul 2008 21:23:55 -0500   author:   H?ctor Miguel

Re: Find a color value   

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. 
> 
> 
>

date: Thu, 10 Jul 2008 03:59:01 -0700   author:   Scott