I don't know what else you have in your code, but one of
the main reasons a loop takes long to run is that
everytime you change what is in a cell, everything
dependant on that cell recalculates. So to speed up any
piece of program code, add this before the loop.
Application.Calculation=xlCalculationManual

Then once all is done,
Application.Calculation=xlCalculationAutomatic


Another thing that slows things down is the screen
updating. So one more thing to speed up operation:
Application.ScreenUpdating = False at the beginning of a
routine, and Application.ScreenUpdating = True at the end.

Use both of these and your code could execute 1000 times
faster.




-----Original Message-----
Hello everyone
I have written some code to change green cells in which
formulas have been entered into values (= PasteSpecial
Paste:=xlPasteValues). The only problem is, this code
takes very long to execute, since it is doing the
operation cell for cell. Is is possible to address the
entire range of green cells in a sheet, and to do a copy
and pastespecial (the cells are not necessarily in one
block)?
Any help to speed this up would be appreciated.

On Error Resume Next
Application.FindFormat.Clear
Application.FindFormat.Interior.ColorIndex = 35
Set celltofind = Cells.Find(what:="",
searchformat:=True)
firstcell = celltofind.Address
celltofind.Value = celltofind.Value
Do
Set celltofind = Cells.Find(after:=celltofind,
what:="", searchformat:=True)
celltofind.Value = celltofind.Value
Loop While Not celltofind.Address = firstcell
Application.FindFormat.Clear
.