Help With My Simple Problem

Someone please help. I need to write a macro that will allow me to paste special, "values" for multiple selctions? In other words, I have a sheet that has about 5 sections that are color coded in yellow and this is where I do a Vlookup to get all the data. Once the data is filled in, I need to just run a macro that will take all the cells within the yellow highlighted section and change them to the value in the cell (removing the formula) like a paste special values? The problem is that I have to do it one row at a time because Excel wont let me paste special for multiple selections? Is there a way around this? The rows are not simulateous and I have to scrol through a long sheet and highlight the rows one by one and then do a paste specail one by one. Im killing myself slowly!! Please help!!

Seems like there should be a way to do this without macros, but I don't know it. :)

This works, anyway:

Dim thisCell As Range
For Each thisCell In thisSheet.UsedRange
With thisCell
If .Interior.ColorIndex = targetIndex Then .Value = Application.Evaluate(.Formula)
End With
Next

thisSheet is the name of the worksheet in question, targetIndex is the color index of the section's backcolor. The easiest way to find out that value is to use macro recorder and fill some cell with that color, then look at the produced code.
This doesn't use the PasteSpecial method, which one could also do, but directly replaces the cell's .Value by the result of its .Formula.

Hope that helps!

The rows are not simulateous and I have to scrol through a long sheet and highlight the rows one by one and then do a paste specail one by one. Im killing myself slowly!! Please help!!

When you work through your worksheet, what determines whether or not a cell should be highlighted? Are you just highlighting cells that contain the VLOOKUP worksheet function? Or are you just highlighting cells that contain formulas? And are you highlighting the cells just so you can see which ones need to be copied+pasted or does the highlighting serve another purpose? More details please..... !! :)

Its hard to explain but the highlighted sells are part of an area where the rest of of the sheet pulls from. Long story short, we load in a forecast into different areas of the sheet (which are highlighted) and then the rest of the sheet pulls from these cells and uses the values as part of more formulas.

Its hard to explain but the highlighted sells are part of an area where the rest of of the sheet pulls from. Long story short, we load in a forecast into different areas of the sheet (which are highlighted) and then the rest of the sheet pulls from these cells and uses the values as part of more formulas.

Hi t8jones8,

Okay I appreciate it's hard to explain. Similarly, it's hard for us to give you the *best* solution with the information you've given us!

Anyway, you've already been given a VBA suggestion. The non VBA route would depend on many factors which are unknown to us. Considered re-designing your worksheet, for example?

Good luck.