Very simple function used to work, but not now

[zookeepertx]

A while back, I needed to be able to sort really long spreadsheets by the color that the cells were. So, someone showed me to create the following function:

Function GetColor(Mycell As Range)
GetColor = Mycell.Interior.ColorIndex
End Function

I would go to the first empty column to the right of my data & key in =GetColor(C7) & then autofill that down to the end of my data. This would fill the cells in that column with the number representing the color in C7 & I could then sort by that column.
Each month, I save the spreadsheet again in a new folder, for the new month's work & it has all worked perfectly for over a year! Now, suddenly, I get the error #NAME? in that column!
I wonder how one of my favorite "toys" got busted?? Can anyone help me fix it? I'll beg & grovel, if I have to, LOL!

 

[kpark91]

lol. Hello there again.
The reason why you're getting #NAME? error is because you have custom color formatted.

The .colorIndex is a short list of different default colors set by MS Excel so if you have any other colors even the slightest, it will give an error.

I would suggest using a more elaborate function or change the cell colors to match the color index.

 

[kpark91]

Chip Pearson has much more elaborate functions which I barely understand.
http://www.cpearson.com/excel/colors.aspx

but I don't see any functions we could use.

 

[zookeepertx]

But, I'm just using the regular fill colors that are on the menu. And haven't changed the colors on the sheet since last month, when it still worked. Maybe I offended it with the new, amazing macros & its feelings are hurt, heheh.

Jenny

 

[kpark91]

loll.
Well, I've tried your UDF and it seemed to work on every color
so I retract my statement about the reason behind error.

and Now, I am confused. :S

Are you having trouble with ALL the cells now?

 

[zookeepertx]

RuhRoh! If YOU'RE confused, I'm in BIG trouble!
Yeah, the error runs all the way down the column. I think this happened one other time & I can't remember how it got fixed. I was just flailing around trying stuff & then it worked again. I may have resorted to retyping the function's coding, but I'm not sure.

Jenny

 

[kpark91]

hm. Try going to
Excel Options from the Top Left Menu > Formulas > Workbook Calculation > Automatic > OK

Make sure your macros are enabled as well.

 

[zookeepertx]

That is set on Automatic. I assume macros are enabled; it doesn't ask me that when I open it.

 

[kpark91]

Just check the macro setting just in case:
Excel Options > Trust Center > Trust Center > Macro Settings > Enable all macros (if you want other options are also available) > OK > OK > Close Workbook > Open Workbook > Test

 

[kpark91]

Also, check if you're only referring to one cell when you're using the formula.