Code stops unexpectedly (Excel 2007)

[jmitchells5w]

I have the following code in a module which basically changes the format of a selection of cells to General, inserts formulas, and then changes the format of the cells to Text. When I use F8 to step through the macro is executes Selection.NumberFormat = "General" and then stops. If I remove the code to change the formats it steps all the way through but I want to change the formats before and after inserting the formulas. I am using Excel 2007. When I copy this code into Excel 2003 it works fine. What am I missing?

Sub ResetVendorNumbers()
'
' ResetVendorNumbers Macro
'
Range("C5:M5").Select
Selection.NumberFormat = "General"
Range("C5").Select
Range("C5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(B2),FALSE)"
Range("D5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(C2),FALSE)"
Range("E5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(D2),FALSE)"
Range("F5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(E2),FALSE)"
Range("G5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(F2),FALSE)"
Range("H5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(G2),FALSE)"
Range("I5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(H2),FALSE)"
Range("J5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(I2),FALSE)"
Range("K5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(J2),FALSE)"
Range("L5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(K2),FALSE)"
Range("M5").Formula = "=VLOOKUP($C$3,VendorNum,COLUMN(L2),FALSE)"
Range("C5:M5").Select
Selection.NumberFormat = "@"
Range("C5").Select

End Sub

 

[Derek Brown]

The code works OK for me using Excel 2007 - as long as the worksheet is not protected, in which case it does fail at that point - also on the other Selection.NumberFormat line.

Exactly the same happens for me in Excel 2003.

Are you getting an error? Or has it 'appeared' to stop because it is recalculating the workbook?

 

[jmitchells5w]

I think the problem may be with the conditional formatting I have on the cells that I am trying to work with. If I have a conditional format in place then the code stops as mentioned. If there is no conditional formatting then the code is executed just fine. I haven't tested it yet but I'm assuming if I clear the conditional formatting and then reapply it at the end of the code then it will solve my problem. I'm not sure why it does this but this is the only solution I've been able to come up with.