how to see the first cell in a row also copy to a new sheet

[elmnas]

Hi guys,

I have problem,

I need to find out which column the cell is in,
the name of the column is always on the first row,
then copy the first cell in the column + the founded cell into a new sheet,
on same place.


I have got this code to loop through all sheets also the cells,
and the code tells me which row the cell is but not the column,


See code below:


Sub my_for_loop3()
For Each sht In ActiveWorkbook.Worksheets
'MsgBox sht.Name






Set Rng = sht.UsedRange
For Each ColorCellID In Rng
If ColorCellID.Interior.ColorIndex = 23 Then

'MsgBox "Value Found in Cell " & Colorcell.Address & _
'" and the Cell Column Number is " & Colorcell.Column
MsgBox "the cell row is " & ColorCellID.Row
MsgBox "cellen är have column" (and some code)


End If





' Colorcell.Interior.ColorIndex = 45
' MsgBox "färgen finns inte"
Next

Next sht
End Sub




Could someone help me ?

Thank you in advance

[elmnas]

I solved a part of the problem by use

MsgBox "en ligger i kolomnen: " & ColorCellID.End(xlUp)(0).Text

how do I copy this to the same place in a new sheet?


Strange doesnt work...

[Yongle]

If you are obtaining the row number from MsgBox "the cell row is " & ColorCellID.Row them column number would be found with & ColorCellID.Column (Your message box is using & ColorCell.Column)

Hope this helps
Yon