Function to return interior colour of a cell

Hi I am trying to write a function to return the interior colour of a cell
which I reference. I am having difficulty getting this to work.

Basically I want to define a function like CKCellColour, use this in the
excel worksheet as a formula like =CKCellColour(M38)

Then have this function return the interior colour of cell M38

The function I have defined is as follows:

Function CKCellColour(CellRef as Range)

Range(CellRef).Select
CKCellColour = Range(CellRef).Interior.ColorIndex

End Function

This function never seems to get the range ref. Can someone help me solve
this problem.

Thanks for your help in advance.

Hi G Man,

Try this adaptation;

'=============>>
Function CKCellColour(CellRef As Range)

CKCellColour = CellRef.Interior.ColorIndex

End Function
'<<=============


--
---
Regards,
Norman



"G Man" <[email protected]> wrote in message
news:[email protected]...
> Hi I am trying to write a function to return the interior colour of a cell
> which I reference. I am having difficulty getting this to work.
>
> Basically I want to define a function like CKCellColour, use this in the
> excel worksheet as a formula like =CKCellColour(M38)
>
> Then have this function return the interior colour of cell M38
>
> The function I have defined is as follows:
>
> Function CKCellColour(CellRef as Range)
>
> Range(CellRef).Select
> CKCellColour = Range(CellRef).Interior.ColorIndex
>
> End Function
>
> This function never seems to get the range ref. Can someone help me solve
> this problem.
>
> Thanks for your help in advance.
>
>

Try:

Function CKCellColour(CellRef As Range)
CKCellColour = CellRef.Interior.ColorIndex
End Function

"G Man" wrote:

> Hi I am trying to write a function to return the interior colour of a cell
> which I reference. I am having difficulty getting this to work.
>
> Basically I want to define a function like CKCellColour, use this in the
> excel worksheet as a formula like =CKCellColour(M38)
>
> Then have this function return the interior colour of cell M38
>
> The function I have defined is as follows:
>
> Function CKCellColour(CellRef as Range)
>
> Range(CellRef).Select
> CKCellColour = Range(CellRef).Interior.ColorIndex
>
> End Function
>
> This function never seems to get the range ref. Can someone help me solve
> this problem.
>
> Thanks for your help in advance.
>
>

Many thanks this does the job nicely.

One last small question, is there a way for to get the recalc function to
rerun this function so that it picks up changes of interior cells given
conditional formating?

"Norman Jones" wrote:

> Hi G Man,
>
> Try this adaptation;
>
> '=============>>
> Function CKCellColour(CellRef As Range)
>
> CKCellColour = CellRef.Interior.ColorIndex
>
> End Function
> '<<=============
>
>
> --
> ---
> Regards,
> Norman
>
>
>
> "G Man" <[email protected]> wrote in message
> news:[email protected]...
> > Hi I am trying to write a function to return the interior colour of a cell
> > which I reference. I am having difficulty getting this to work.
> >
> > Basically I want to define a function like CKCellColour, use this in the
> > excel worksheet as a formula like =CKCellColour(M38)
> >
> > Then have this function return the interior colour of cell M38
> >
> > The function I have defined is as follows:
> >
> > Function CKCellColour(CellRef as Range)
> >
> > Range(CellRef).Select
> > CKCellColour = Range(CellRef).Interior.ColorIndex
> >
> > End Function
> >
> > This function never seems to get the range ref. Can someone help me solve
> > this problem.
> >
> > Thanks for your help in advance.
> >
> >
>
>
>

Take a look at this link for more info on conditional formatted colors...

http://www.cpearson.com/excel/CFColors.htm

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> That function will not recognize conditional formats. Conditional formats do
> not actually set the interior color of the cell. Application.volitile will
> ensure that the function is re-evelauted every time the spreadsheet is
> recalced. Place that at the top of the code.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "G Man" wrote:
>
> > Many thanks this does the job nicely.
> >
> > One last small question, is there a way for to get the recalc function to
> > rerun this function so that it picks up changes of interior cells given
> > conditional formating?
> >
> > "Norman Jones" wrote:
> >
> > > Hi G Man,
> > >
> > > Try this adaptation;
> > >
> > > '=============>>
> > > Function CKCellColour(CellRef As Range)
> > >
> > > CKCellColour = CellRef.Interior.ColorIndex
> > >
> > > End Function
> > > '<<=============
> > >
> > >
> > > --
> > > ---
> > > Regards,
> > > Norman
> > >
> > >
> > >
> > > "G Man" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi I am trying to write a function to return the interior colour of a cell
> > > > which I reference. I am having difficulty getting this to work.
> > > >
> > > > Basically I want to define a function like CKCellColour, use this in the
> > > > excel worksheet as a formula like =CKCellColour(M38)
> > > >
> > > > Then have this function return the interior colour of cell M38
> > > >
> > > > The function I have defined is as follows:
> > > >
> > > > Function CKCellColour(CellRef as Range)
> > > >
> > > > Range(CellRef).Select
> > > > CKCellColour = Range(CellRef).Interior.ColorIndex
> > > >
> > > > End Function
> > > >
> > > > This function never seems to get the range ref. Can someone help me solve
> > > > this problem.
> > > >
> > > > Thanks for your help in advance.
> > > >
> > > >
> > >
> > >
> > >

That function will not recognize conditional formats. Conditional formats do
not actually set the interior color of the cell. Application.volitile will
ensure that the function is re-evelauted every time the spreadsheet is
recalced. Place that at the top of the code.
--
HTH...

Jim Thomlinson


"G Man" wrote:

> Many thanks this does the job nicely.
>
> One last small question, is there a way for to get the recalc function to
> rerun this function so that it picks up changes of interior cells given
> conditional formating?
>
> "Norman Jones" wrote:
>
> > Hi G Man,
> >
> > Try this adaptation;
> >
> > '=============>>
> > Function CKCellColour(CellRef As Range)
> >
> > CKCellColour = CellRef.Interior.ColorIndex
> >
> > End Function
> > '<<=============
> >
> >
> > --
> > ---
> > Regards,
> > Norman
> >
> >
> >
> > "G Man" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi I am trying to write a function to return the interior colour of a cell
> > > which I reference. I am having difficulty getting this to work.
> > >
> > > Basically I want to define a function like CKCellColour, use this in the
> > > excel worksheet as a formula like =CKCellColour(M38)
> > >
> > > Then have this function return the interior colour of cell M38
> > >
> > > The function I have defined is as follows:
> > >
> > > Function CKCellColour(CellRef as Range)
> > >
> > > Range(CellRef).Select
> > > CKCellColour = Range(CellRef).Interior.ColorIndex
> > >
> > > End Function
> > >
> > > This function never seems to get the range ref. Can someone help me solve
> > > this problem.
> > >
> > > Thanks for your help in advance.
> > >
> > >
> >
> >
> >

and this http://www.xldynamic.com/source/xld.CFConditions.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Take a look at this link for more info on conditional formatted colors...
>
> http://www.cpearson.com/excel/CFColors.htm
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Jim Thomlinson" wrote:
>
> > That function will not recognize conditional formats. Conditional
formats do
> > not actually set the interior color of the cell. Application.volitile
will
> > ensure that the function is re-evelauted every time the spreadsheet is
> > recalced. Place that at the top of the code.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "G Man" wrote:
> >
> > > Many thanks this does the job nicely.
> > >
> > > One last small question, is there a way for to get the recalc function
to
> > > rerun this function so that it picks up changes of interior cells
given
> > > conditional formating?
> > >
> > > "Norman Jones" wrote:
> > >
> > > > Hi G Man,
> > > >
> > > > Try this adaptation;
> > > >
> > > > '=============>>
> > > > Function CKCellColour(CellRef As Range)
> > > >
> > > > CKCellColour = CellRef.Interior.ColorIndex
> > > >
> > > > End Function
> > > > '<<=============
> > > >
> > > >
> > > > --
> > > > ---
> > > > Regards,
> > > > Norman
> > > >
> > > >
> > > >
> > > > "G Man" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi I am trying to write a function to return the interior colour
of a cell
> > > > > which I reference. I am having difficulty getting this to work.
> > > > >
> > > > > Basically I want to define a function like CKCellColour, use this
in the
> > > > > excel worksheet as a formula like =CKCellColour(M38)
> > > > >
> > > > > Then have this function return the interior colour of cell M38
> > > > >
> > > > > The function I have defined is as follows:
> > > > >
> > > > > Function CKCellColour(CellRef as Range)
> > > > >
> > > > > Range(CellRef).Select
> > > > > CKCellColour = Range(CellRef).Interior.ColorIndex
> > > > >
> > > > > End Function
> > > > >
> > > > > This function never seems to get the range ref. Can someone help
me solve
> > > > > this problem.
> > > > >
> > > > > Thanks for your help in advance.
> > > > >
> > > > >
> > > >
> > > >
> > > >

Great reference Bob. I had not seen that one. The author sound fimilair so I
guess I will trust the code.
--
HTH...

Jim Thomlinson


"Bob Phillips" wrote:

> and this http://www.xldynamic.com/source/xld.CFConditions.html
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > Take a look at this link for more info on conditional formatted colors...
> >
> > http://www.cpearson.com/excel/CFColors.htm
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > That function will not recognize conditional formats. Conditional
> formats do
> > > not actually set the interior color of the cell. Application.volitile
> will
> > > ensure that the function is re-evelauted every time the spreadsheet is
> > > recalced. Place that at the top of the code.
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "G Man" wrote:
> > >
> > > > Many thanks this does the job nicely.
> > > >
> > > > One last small question, is there a way for to get the recalc function
> to
> > > > rerun this function so that it picks up changes of interior cells
> given
> > > > conditional formating?
> > > >
> > > > "Norman Jones" wrote:
> > > >
> > > > > Hi G Man,
> > > > >
> > > > > Try this adaptation;
> > > > >
> > > > > '=============>>
> > > > > Function CKCellColour(CellRef As Range)
> > > > >
> > > > > CKCellColour = CellRef.Interior.ColorIndex
> > > > >
> > > > > End Function
> > > > > '<<=============
> > > > >
> > > > >
> > > > > --
> > > > > ---
> > > > > Regards,
> > > > > Norman
> > > > >
> > > > >
> > > > >
> > > > > "G Man" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Hi I am trying to write a function to return the interior colour
> of a cell
> > > > > > which I reference. I am having difficulty getting this to work.
> > > > > >
> > > > > > Basically I want to define a function like CKCellColour, use this
> in the
> > > > > > excel worksheet as a formula like =CKCellColour(M38)
> > > > > >
> > > > > > Then have this function return the interior colour of cell M38
> > > > > >
> > > > > > The function I have defined is as follows:
> > > > > >
> > > > > > Function CKCellColour(CellRef as Range)
> > > > > >
> > > > > > Range(CellRef).Select
> > > > > > CKCellColour = Range(CellRef).Interior.ColorIndex
> > > > > >
> > > > > > End Function
> > > > > >
> > > > > > This function never seems to get the range ref. Can someone help
> me solve
> > > > > > this problem.
> > > > > >
> > > > > > Thanks for your help in advance.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
>
>
>