Allowing manual entering in cells with formulas

Hello!

So I found this very cool thread on here called Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formula. The thread was marked as solved but I am having trouble making it work for me. I am hoping someone can help me.

The thread teaches us how to override a formula in a cell and then when we delete the override, the original formula goes back. This is the explanation:

Re: Allowing Users to Input Data into Cells Containing Formulas without Deleting the Formula
I think I figured it out. I don't mean to step on your toes ChemistB, but this is a pretty sweet idea and I'd like to make it work too.

I right clicked on Template tab and made a copy. I named it Template - Hidden.

Back in VB land I pasted this onto Template's sheet. (the original)

Copy to clipboard

Please Login or Register  to view this content.

Then I added Conditional Formatting to C1 as formula: =NOT(Template!C1='Template - Hidden'!C1)

and then used the Format Painter to spread it everywhere else.

All that's left is to hide the 'Hidden' sheet. Did I miss anything?

I did all of this and I am getting an error with my formula. When I clear the original cell I can type in whatever, and then when I delete that to make it go back to the original, it gives me an error and asks me to debug the error. I do that and it tells me this line is wrong: Target.Formula = Sheets("Template - Hidden").Cells(Target.Row, Target.Column).Formula
What do I do???

I attached a copy of the workbook to help.

Just another note: My range is C7:C46 and in these cells I have numbers like T009, T083, T890 like product numbers... Could this be the problem? So maybe I need to change this line: If Target.Count > 1 Then Exit Sub to not be >1?

Basically I have a list of product numbers that are being pulled from another worksheet with a look formula.What I want to write is a code that allows someone to clear that cell and type in a new product number. Then if they remove their typing, the cell reverts back to the original number using the original formula...

Thank you, but it still doesn't work... Could it be because my original formula is an array formula? It allows me to type into the cell, and then when I erase it, the formula comes back non array and gives me a 0.