Function to return interior colour of a cell

[G Man]

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.

 

[Norman Jones]

Hi G Man,

Try this adaptation;

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

CKCellColour = CellRef.Interior.ColorIndex

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

 

[Toppers]

Try:

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

 

[G Man]

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?

 

[Jim Thomlinson]

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

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

 

[Jim Thomlinson]

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.

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jim Thomlinson]

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