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.

Function to return interior colour of a cell
 

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.

Function to return interior colour of a cell
 

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.

Function to return interior colour of a cell
 

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.

Function to return interior colour of a cell
 

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.

Function to return interior colour of a cell
 

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.

Function to return interior colour of a cell
 

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.

Function to return interior colour of a cell
 

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.