function to extract background color from one cell to another

[Francois via OfficeKB.com]

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 !

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)

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

 

[Francois via OfficeKB.com]

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

 

[Bob Phillips]

That is probably because your worksheet is not named Status.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

 

[Francois via OfficeKB.com]

That is probably because your worksheet is not named Status.
[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