Help! Calculation Time and changing formula result to a value?

[onp]

Originally, the following Italic quoted Message was what I was trying to accomplice.
And the code given to me based on the criteria works perfect. However, Now the problem I Have is that the further I type in Cell G the longer it takes to calculate.
By the time I reach G200 its just unbearable and I would have to repeat these steps up to G4000 Now my question is:
Is there a different way that I can accomplice the same results and not having the calculations take so long?
I have attached a Sample Sheet with the data and code
Thank you so much for your efforts and the time
Nino

"In this Example Column H and I have text data. in A B C D are formulas.  In The G column I type 1 manually starting from cell G1 and repeat same downwards. As you can see when you type 1 in cell G1 through G12, when you reach cell G12 a result appears in D1,through,D9 while all the other D cells show 0. At this point i would like for any D cell that have a result to be converted into a text value so it will no longer change. If not, and you continue to type 1 in the remainder G cells the results in D1,D2,D3 and all other D cells will keep on changing .Hope i was able to explain what i am trying to accompliceThank you so much for your efforts
NINO"

[G e r a r d]

If you make the area smaller where the code operates, it will be much faster.
Try something like:

Please Login or Register  to view this content.

[onp]

Hi Tiburg,
I tried your code but I get an Error on the following line

For Each cell In Target.Offset(-20, -3).Resize(40, 1)

[G e r a r d]

Strange, I used that in your example and it works fine when I test...

[onp]

Strange, I used that in your example and it works fine when I test...

Hi Tilburg,
It works great
I understud why i was getting an error code. The error would pop up only if i tried to type 1 in cell G20 and smaller.
I changed the value on the target offset code "Target.Offset(-10, -3)" and all works great.

Thank you so much