Just a slight restatement of the situation:

1) The information from the form is not put in the cell FORMATTED as text  -
it IS text information. With text information in a cell, Excel then ignores
any numeric  formatting you might have on the cell. Try creating a cell with
100000 in it, and format  it up with thousands separator and a couple of
decimal places. Now overtype the cell with "Fred" (no quotes) - the numeric
formatting is ignored. Type another number - the numeric formatting is
still there, it was just ignored for "Fred". Now type '123456 (note that
there is a single quote at the beginning and none at the end) - you'll get
no formatting, like with "Fred, and also the little green triangle  to nag
you.

Changing the number format - while you've got a text string in the cell -
won't help you. That's why your VBA wasn't working. Hopefully the above
explanation makes it clear why.

2) The little triangle is not actually error checking. In fact, the
situation you have is not actually an error (I use numbers  as text all the
time). It's simply an informative message to alert you to this situation.

So, the best way to do this is to get the form to put a numeric value into
the cell. This means that it will need to convert the text string from the
input box into a number and store it. Use the Val () function to do the
conversion.

Or, look at your VBA and do a conversion of the contents of the cell, not
its formatting. Something like

Range("I8").Formula = Val(Range("I8").Value)

I didn't have a proper number-as-text example to test, but this worked fine
for a cell with '123456 in it. (Actually, it also worked fine without the
"Val ()" and with any combinations of .Formula and .Value.) Have a play
with variations on this statement until you get one you like.