User Defined Function Stops Working When Renaming File

[cluffj]

I have an excel file that is exported weekly that I need to convert certain cell colors into other colors. To accomplish this I found a User Defined Function =identifycolor (seen below) that I wrote into the conditional formatting to change the color of a cell if it met one of the color codes. This worked beautifully until I tried to save a copy of the file and send it to a coworker. When I got into VBA in the new file the UDF is still there but all of my cell colors are wrong. How do I fix this?

Function IdentifyColor(CellToTest As Range)

'Returns R + (256 * G) + (65536 * B)
'IdentifyColor = 255 for red, 65280 for green, etc.
IdentifyColor = CellToTest.Interior.Color

End Function

UDF working:

UDF not working:

 

[RoryA]

You'd have to clarify the CF rules, since your UDF doesn't do any colouring itself. Are you sure they enabled macros?

 

[Joe4]

Welcome to the Board!

In addition to what Rory asked, how exactly are you renaming the file?
Are you renaming it to an extension which allows VBA (i.e. "xlsm" or "xlsb")?

 

[cluffj]

You'd have to clarify the CF rules, since your UDF doesn't do any colouring itself. Are you sure they enabled macros?

Also adding @Joe4

Screenshot below of the Conditional Format rules. Also, this is saved as a .xlsm.

They are enabling macros, however I'm curious if I can copy the formatting and paint it to the new document to avoid them needing to do this step. The deliverable just needs to have the final colors and dates.

 

[cluffj]

Welcome to the Board!

In addition to what Rory asked, how exactly are you renaming the file?
Are you renaming it to an extension which allows VBA (i.e. "xlsm" or "xlsb")?

Right now I've just tried right clicking the file and copy/paste.

 

[Joe4]

Right now I've just tried right clicking the file and copy/paste.

So it appears that maybe you are copying/pasting ranges.
Why not just a file "SaveAs"?
That should make a complete copy of the entire workbook, formatting and VBA included.