Generating calculations based on certain cells

[Klartigue]

Basically, for each item highlighted, A1, A2, A3, A4..etccc..I calculate the impact of the trade by multiplying Spread*Amount*10. So can you help me write a VBA code that calculates the impact for each item highlighted? Now there could be 20 items highlighted or there could be 8 items highlighted, it will always differ so I need a code that does this for all items highlighted, and then place the results two lines below the last line.

Thanks for the help! ... You all helped me write a code that generated the top part of this spreadsheet and I use it all the time, so helpful!!!

[mdmackillop]

[vba]
Option Explicit
Sub Test()
Dim r As Range
Dim Tgt As Range, cel As Range
Dim i As Long
Set r = Columns(4).SpecialCells(xlCellTypeConstants)
Set Tgt = Cells(Rows.Count, 4).End(xlUp)(4)
For Each cel In r
If cel.Interior.ColorIndex = 6 Then
Tgt.Offset(i).Value = cel.Value
Tgt.Offset(i, 1).FormulaR1C1 = "=R" & cel.Row & "C7 * R" & cel.Row & "C20 * 10"
i = i + 1
End If
Next
End Sub

[/vba]

[Klartigue]

THank you that works great!!