If a condition is met convert range to value from formula

Good Afternoon All

First I just want to thank everyone on here regardless of solution. I have been following this board for a while and this site has always helped me expand my knowledge of VBA. I am no expert by any means but i now enough to get myself into trouble.

Image if you will of a spreadsheet where in column A you have a drop down with two options to pick from open and close.

Columns B - E have formulas in them that pull from another sheet (specifically vlookup formulas)

What i am trying to accomplish is this. If a person selects close from the drop down in column A, columns (B-E), on that row, gets converted from the vlookup formula to its value.

I have tried writing this a number of way but either excel says that there is a problem with the code or i get the wrong result. I just can't seem to get it to specify to the specific rows that meet the condition. This is the closest working code that i have so far:

Please Login or Register  to view this content.

I have tried specifying range for rows but I am doing something wrong.

If someone can help point me in the right direction with this that would be amazing.
Thank you all.
MG

Post withdrawn

The only thing I did was add

Please Login or Register  to view this content.

to your code and it seemed to work

not a problem

possible solution you can use below

Please Login or Register  to view this content.

Mock up file attached

just to explain my code further

the "i" in my code represents the "current" row

Please Login or Register  to view this content.

so it starts from row one (i = 1) and loops until "ActiveSheet.UsedRange.Rows.Count" which should represent your last row
this helps a bit in the code running faster so it doesnt loop for million+ rows

syntax for cells (rows,columns)

so this line

Please Login or Register  to view this content.

looks at column 1 which is A

i = 1 then a1, i = 2 then a2, i = 3 then a3....etc

rest of the code is self explanatory

Please Login or Register  to view this content.

this just copies the value and replaces with value ...its another way of doing pastespecial - values