Custom function not working automatically

[Naz555]

Hi All,

I am desperately in need of your excel help. I have the below custom function:

Public Function GetFillColor(range) As Long
Application.Volatile
GetFillColor = range.Interior.ColorIndex
End Function

I am working on an intelligent rostering spreadsheet which identifies a certain sector based on the color coding of a time range. I am suing the above function to return the color index of the cell range which in order helps derive a logical function to achieve my purpose. However, in a selected range, a large number of cells are blank cells, therefore, this function doesnt work automatically - it requires the user to hit enter or change another cell in the spreadsheet. these logical functions in turn populate a summary sheet elsewhere, therefore, the functions need to be live. In order to circumvent this problem, i tried to incorporate a live clock into the sheet which updated per second - the logic was this ensures the sheet stays live by the second. however, it has introduced other problems - the clock stops everytime a macro is run and also the screen and the cursor is pulsing every second which is very annoying. however, if this custom function can be run withoiut any intervention - it solves all my problems. Could someone please show me the way to achieve this? Many thanks in advance!!

[Kenneth Hobson]

Changing the interior color does not trigger an event. You might be able to live with a sheet selection event to force a sheet calculation. Right click sheet's tab, View Code, and paste.

Please Login or Register  to view this content.

I would suggest changing the input to a different variable name and set type as Range. e.g.

Please Login or Register  to view this content.

[mjr veverka]

Public Function GetFillColor(range) As Long

"range" is a reserved word in vba - Range, as Kenneth Hobson wrote

ColorIndex of blank cell is -4142 (old excel)

[Naz555]

Thanks heaps guys! That was very very helpful! It works! legends!!

[Naz555]

hi Kenneth, i have just realised that using ActiveSheet.Calculate has resulted in the inability to copy paste within the sheet. Is there a workaround for this please?