Data from another worksheet won't automatically change into desired format (Accounting)

[Grumps]

I'm using Microsoft Excel 2010 on Windows 7 64-bit.
I have a worksheet where I have different products with their prices from 2 different stores. I have 3 worksheets altogether - this front one where I can see the stuff I need, one for one shop, and one for the other shop. For each product I have data from the web, and then on my "front page" (where I see only what I need to), I have referenced the cells with the prices.

So, I run some macros to tidy up these cells - there is nothing left except for a number. No spaces, no non-breaking spaces, nothing. However, the numbers that are referenced on my front page stay as just numbers, and don't automatically go into the Accounting format. This only happens when I go into my data worksheets, find the cells where the prices are, click in the top bar when you can enter the info/data, and press enter.

This is quite annoying as I also have columns for the price difference, best price, and the store which is cheapest. But of course none of these columns work because the numbers won't go into Accounting format.

Is there some sort of macro I could use that could "press enter" on the cells I need it to? I tried making a macro myself of finding the cells, and pressing enter, but then of course the macro saves what the data was at the current time of the macro. So when I update the web sources, and run the macros to clean it up, and the macro to press enter on the cells, it just enters the old data again. Maybe there's a way of getting rid of the specificity of what goes in the cells, and rather just allowing whatever is currently in there?

I have looked everywhere, all day. I can't find anything similar or helpful.

Thanks in advance for any help.

Josh

 

[ChrisM]

You'll have to loop through all the cells to use the val function. Can you set your Mindfactory code similar to your AMARemoveEUR code? That would solve the issue. Basically you will need to identify the range of data, you don't want to loop through every cell on the worksheet (millions of them).