function to extract background color from one cell to another

Hi all,
Doe's anyone know if a function/addin etc exists that will enable me to get
the background color from a cell and then copy it to the current cell

eg someting like in sheet 1 cell A put the formula =backgroundcolor(sheet2!
A1) then replicate to other cells

I can't use the normal method of copy/paste special as I need to do it for a
LOT of cells

Lotus 123 used to have an addin called @setstyle ....I'm after something
similar

Thanks to all who have helped me on previous posts.....even if you didn't
know you had !

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200603/1

VBA?

iLastRow = Cells(Rows.Count).End(xlUp).Row
For i = 1 To iLastRow
Cells(i,"C").Interior.Colorindex = Cells(i,"A").Interior.Colorindex
Next i

this copies the colour from A to C

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Francois via OfficeKB.com" <u18959@uwe> wrote in message
news:5e1167fa013d5@uwe...
> Hi all,
> Doe's anyone know if a function/addin etc exists that will enable me to
get
> the background color from a cell and then copy it to the current cell
>
> eg someting like in sheet 1 cell A put the formula
=backgroundcolor(sheet2!
> A1) then replicate to other cells
>
> I can't use the normal method of copy/paste special as I need to do it for
a
> LOT of cells
>
> Lotus 123 used to have an addin called @setstyle ....I'm after
something
> similar
>
> Thanks to all who have helped me on previous posts.....even if you didn't
> know you had !
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200603/1

Bob Phillips wrote:
>VBA?
>
> iLastRow = Cells(Rows.Count).End(xlUp).Row
> For i = 1 To iLastRow
> Cells(i,"C").Interior.Colorindex = Cells(i,"A").Interior.Colorindex
> Next i
>
>this copies the colour from A to C
>
>> Hi all,
>> Doe's anyone know if a function/addin etc exists that will enable me to get
>[quoted text clipped - 11 lines]
>> Thanks to all who have helped me on previous posts.....even if you didn't
>> know you had !





Hi Bob,

Thanks for the very quick reply.

The destination cells for this 'function' are on a 'status' worksheet, which
lists data from several other worksheets, so I don't believe that I could use
VBA easily.
I found this from Chip Pearsons site but I'm having trouble getting it to
work (I'm sure it's me)


Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

But when I put the range in (A1:T90) or whatever I got some sort of
compile/syntax error on the Function line.
as I say , I know it's me but as you may guess I'm not clued up on much of
VBA

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200603/1

You have to use VBA, you cannot set the colour on another sheet using a
function. Chip's function just returns the cell colorindex, it does not set
any others.

My code easily adapts to another sheet

iLastRow = Cells(Rows.Count).End(xlUp).Row
For i = 1 To iLastRow
Worksheets("Status").Cells(i,"C").Interior.Colorindex =
Cells(i,"A").Interior.Colorindex
Next i


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Francois via OfficeKB.com" <u18959@uwe> wrote in message
news:5e11f1deeec06@uwe...
> Bob Phillips wrote:
> >VBA?
> >
> > iLastRow = Cells(Rows.Count).End(xlUp).Row
> > For i = 1 To iLastRow
> > Cells(i,"C").Interior.Colorindex =
Cells(i,"A").Interior.Colorindex
> > Next i
> >
> >this copies the colour from A to C
> >
> >> Hi all,
> >> Doe's anyone know if a function/addin etc exists that will enable me to
get
> >[quoted text clipped - 11 lines]
> >> Thanks to all who have helped me on previous posts.....even if you
didn't
> >> know you had !
>
>
>
>
>
> Hi Bob,
>
> Thanks for the very quick reply.
>
> The destination cells for this 'function' are on a 'status' worksheet,
which
> lists data from several other worksheets, so I don't believe that I could
use
> VBA easily.
> I found this from Chip Pearsons site but I'm having trouble getting it to
> work (I'm sure it's me)
>
>
> Function CellColorIndex(InRange As Range, Optional _
> OfText As Boolean = False) As Integer
> '
> ' This function returns the ColorIndex value of a the Interior
> ' (background) of a cell, or, if OfText is true, of the Font in the cell.
> '
> Application.Volatile True
> If OfText = True Then
> CellColorIndex = InRange(1,1).Font.ColorIndex
> Else
> CellColorIndex = InRange(1,1).Interior.ColorIndex
> End If
>
> End Function
>
> But when I put the range in (A1:T90) or whatever I got some sort of
> compile/syntax error on the Function line.
> as I say , I know it's me but as you may guess I'm not clued up on much of
> VBA
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200603/1

Bob Phillips wrote:
>You have to use VBA, you cannot set the colour on another sheet using a
>function. Chip's function just returns the cell colorindex, it does not set
>any others.
>
>My code easily adapts to another sheet
>
> iLastRow = Cells(Rows.Count).End(xlUp).Row
> For i = 1 To iLastRow
> Worksheets("Status").Cells(i,"C").Interior.Colorindex =
>Cells(i,"A").Interior.Colorindex
> Next i
>
>> >VBA?
>> >
>[quoted text clipped - 40 lines]
>> as I say , I know it's me but as you may guess I'm not clued up on much of
>> VBA


Thanks for your patience Bob, but I get a 'subscript out of range' on the
worksheet row

--
Message posted via http://www.officekb.com

That is probably because your worksheet is not named Status.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Francois via OfficeKB.com" <u18959@uwe> wrote in message
news:5e129801347c8@uwe...
> Bob Phillips wrote:
> >You have to use VBA, you cannot set the colour on another sheet using a
> >function. Chip's function just returns the cell colorindex, it does not
set
> >any others.
> >
> >My code easily adapts to another sheet
> >
> > iLastRow = Cells(Rows.Count).End(xlUp).Row
> > For i = 1 To iLastRow
> > Worksheets("Status").Cells(i,"C").Interior.Colorindex =
> >Cells(i,"A").Interior.Colorindex
> > Next i
> >
> >> >VBA?
> >> >
> >[quoted text clipped - 40 lines]
> >> as I say , I know it's me but as you may guess I'm not clued up on much
of
> >> VBA
>
>
> Thanks for your patience Bob, but I get a 'subscript out of range' on the
> worksheet row
>
> --
> Message posted via http://www.officekb.com

Bob Phillips wrote:
>That is probably because your worksheet is not named Status.
>
>> >You have to use VBA, you cannot set the colour on another sheet using a
>> >function. Chip's function just returns the cell colorindex, it does not set
>[quoted text clipped - 16 lines]
>> Thanks for your patience Bob, but I get a 'subscript out of range' on the
>> worksheet row




Oops,

Thanks a load

--
Message posted via http://www.officekb.com

This does not work when you have to copy the cells that are conditional formatted based on some other formula.

For mirroring a range of colors using the formula please use.
For C = 2 To 24
For R = 2 To 24
Worksheets("Sheet1").Cells(R, C).Interior.ColorIndex = Cells(R + 39, C).Interior.ColorIndex
Next R

Next C

Here C is columns and R is Rows. you can opy color of all the cells from (41,2) to (2,24)

Let me know if there is a formula to mirror the color after applying conditional formating to the source cells.