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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.