Hi,


…”..one mistake happened from my end in providing the info to u , we have to copy and paste the data after highlighted colours cells…..
This is easy to change: we can copy one cell to the right , .Offset(0, 1)
Change
Code:
         Rng.Offset(0, 1).Resize(, Ws1.UsedRange.Columns.Count - 1).SpecialCells(xlCellTypeFormulas, xlNumbers + xlTextValues).Copy
to
Code:
         Rng.Offset(0, 1).Resize(, Ws1.UsedRange.Columns.Count - 1).SpecialCells(xlCellTypeFormulas, xlNumbers + xlTextValues).Offset(0, 1).Copy


……. If there are no highlighted colour cells in the row then copy paste the first cells of that row …..”….

In my macro, I added a “=“ to the highlighted cells : see http://www.excelfox.com/forum/showth...ll=1#post12570

So we can do another If Else to look for a “=” in the row.
It seems that we can use the Range.Find Method to find any formula if What we look for is
=
So we can use that method to search the row for a first match. If Nothing is returned, then we have no highlighted cell
Like this
Code:
    Dim HigChk As Range
     Set HigChk = Rng.Offset(0, 1).Resize(, Ws1.UsedRange.Columns.Count - 1).Find(What:="=", LookIn:=xlFormulas, LookAt:=xlPart)
        If Not HigChk Is Nothing Then ' we found a highlighted cell -----------
        ' copy the yellow highlighted colured cell data in that row of 1.xlsx
         Rng.Offset(0, 1).Resize(, Ws1.UsedRange.Columns.Count - 1).SpecialCells(xlCellTypeFormulas, xlNumbers + xlTextValues).Offset(0, 1).Copy
        ' paste it to column L OF 2.xlsx
        Else ' case no highlighted cell, so column B should be copüied from 1.xlsx
         Rng.Offset(0, 1).Copy
        End If                        ' we were looking for highligted cell ---


All info and files here: http://www.excelfox.com/forum/showth...ll=1#post12580


Alan



P.S. I did also try macro from ExcelFox. This also seems to give the same results as my macro on your test data.