software

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
www.officekb.com/Uwe/Forums.a...excel/200603/1

VBA?

iLastRow = Cells(Rows.Count).End(xlUp).Row
For i = 1 To iLastRow
Cells(i,quot;Cquot;).Interior.Colorindex = Cells(i,quot;Aquot;).Interior.Colorindex
Next i

this copies the colour from A to C

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Francois via OfficeKB.comquot; lt;u18959@uwegt; wrote in message
news:5e1167fa013d5@uwe...
gt; Hi all,
gt; Doe's anyone know if a function/addin etc exists that will enable me to
get
gt; the background color from a cell and then copy it to the current cell
gt;
gt; eg someting like in sheet 1 cell A put the formula
=backgroundcolor(sheet2!
gt; A1) then replicate to other cells
gt;
gt; I can't use the normal method of copy/paste special as I need to do it for
a
gt; LOT of cells
gt;
gt; Lotus 123 used to have an addin called @setstyle ....I'm after
something
gt; similar
gt;
gt; Thanks to all who have helped me on previous posts.....even if you didn't
gt; know you had !
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...excel/200603/1
Bob Phillips wrote:
gt;VBA?
gt;
gt; iLastRow = Cells(Rows.Count).End(xlUp).Row
gt; For i = 1 To iLastRow
gt; Cells(i,quot;Cquot;).Interior.Colorindex = Cells(i,quot;Aquot;).Interior.Colorindex
gt; Next i
gt;
gt;this copies the colour from A to C
gt;
gt;gt; Hi all,
gt;gt; Doe's anyone know if a function/addin etc exists that will enable me to get
gt;[quoted text clipped - 11 lines]
gt;gt; Thanks to all who have helped me on previous posts.....even if you didn't
gt;gt; 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
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(quot;Statusquot;).Cells(i,quot;Cquot;).Interior.Colorin dex =
Cells(i,quot;Aquot;).Interior.Colorindex
Next i--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

--
Message posted via www.officekb.com

That is probably because your worksheet is not named Status.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

Oops,

Thanks a load

--
Message posted via www.officekb.com