Hello VBA users,

I used the macro recorder to create a macro that transfers cell values from Database.xls to the CDF.xls. Right now it only transfers the values of the active cell (in the Database.xls) and the 4 cells next to it in the same row, to the active cell in the CDF.xls next to each other. This works fine.

Now I want to do the same actions but based on the cell color. I cannot use for this the active.cell. Database.xls has several worksheets, e.g. "Ballast", "Coupler". CDF.xls has only one worksheet "CDF".
Now the macro should look if any cells in the column A are highlighted red, then it should copy the cell value of the highlighted cells AND the values of the next 4 cells (these are not highlighted) in the same row to the CDF.xls. The macro should start to paste the 5 values into the CDF.xls at A68, then copy the next set of values to the next row A69. I hope I described it ok.

Many thanks for any help on this, since usually I can cope with the macro recorder and modify it with simple code.


This is the macro I use till now:

Sub Versuch()

Selection.Copy
Windows("CDF.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("Database.xls").Activate
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("CDF.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("Database.xls").Activate
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("CDF.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 2).Range("A1").Select
Windows("Database.xls").Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("CDF.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, -4).Range("A1").Select
Windows("Database.xls").Activate
Application.CutCopyMode = False
ActiveCell.Offset(1, -4).Range("A1").Select
End Sub

Configuration: Windows XP
Firefox 3.0.1