You've written a function, not a macro; you need to call it from a
macro, or use it as a spreadsheet function  in a cell:

run this macro  to test:
Sub blah()
MsgBox CellColorIndex(Range("A1"))
End Sub

(If you want to step through it, put a breakpoint within the function
at, say, the line 'Application.Volatile True' and run the 'blah'
macro, then press F8 repeatedly to step through the function.)

in the worksheet:
=CellColorIndex(A1)