I have a column with data in it. The column grows over
time during a sub routine (i.e. it get longer) I want to
pass the first cells (say A1) interior colour to a
variable. However none of the cells in the column are
named except the most recent addition (say A30). I want
to be able to take the named cell extract from that the
first cell in that columns address. Similar to doing an
xlUp from the named cell.

Is the xlUP the only way I can do this. And if so, what
do I do after the xlUp to actuallly select the first
cell. I was hoping I could do this without selecting
cells.

Thanks

Here it is... xlUp may not work for you if you had a blank cell in the column
so I used the intersect method...

Option Explicit

Sub test()
MsgBox GetCellColour
End Sub

Public Function GetCellColour() As Long
GetCellColour = Intersect(ActiveSheet.Rows(1),
ActiveCell.EntireColumn).Interior.ColorIndex
End Function

HTH

"ExcelMonkey" wrote:

I have a column with data in it. The column grows over
time during a sub routine (i.e. it get longer) I want to
pass the first cells (say A1) interior colour to a
variable. However none of the cells in the column are
named except the most recent addition (say A30). I want
to be able to take the named cell extract from that the
first cell in that columns address. Similar to doing an
xlUp from the named cell.

Is the xlUP the only way I can do this. And if so, what
do I do after the xlUp to actuallly select the first
cell. I was hoping I could do this without selecting
cells.

Thanks

Hi, the folowing line of code will place in a variable the integer
value of the interior color of the first cell in the column of a range
called 'NmdRnge":

myColor = Cells(1, Range(NmdRnge).Column).Interior.ColorIndex

HTH--Lonnie M.

Not sure I fully understand, but here goes

myVar = Cells(1,Range("the_named_cell").Column).Interior.C olorindex

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote in message
...
I have a column with data in it. The column grows over
time during a sub routine (i.e. it get longer) I want to
pass the first cells (say A1) interior colour to a
variable. However none of the cells in the column are
named except the most recent addition (say A30). I want
to be able to take the named cell extract from that the
first cell in that columns address. Similar to doing an
xlUp from the named cell.

Is the xlUP the only way I can do this. And if so, what
do I do after the xlUp to actuallly select the first
cell. I was hoping I could do this without selecting
cells.

Thanks

if the named cell is named Dog then

range("Dog").EntireColumn.cells(1).Interior.Color


--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
I have a column with data in it. The column grows over
time during a sub routine (i.e. it get longer) I want to
pass the first cells (say A1) interior colour to a
variable. However none of the cells in the column are
named except the most recent addition (say A30). I want
to be able to take the named cell extract from that the
first cell in that columns address. Similar to doing an
xlUp from the named cell.

Is the xlUP the only way I can do this. And if so, what
do I do after the xlUp to actuallly select the first
cell. I was hoping I could do this without selecting
cells.

Thanks