Code to cut and paste row in red text

[charllie]

Hi Folks,

If possible i need a code to do the following when a macro button is pressed.

• Check column A in sheet1 and if the text in any row is in red text then cut and paste that whole row to the next empty row in sheet 2.
  
  In other words remove all rows in red text from shett 1 to sheet 2.

I would really appreciate anyones help.

Thanks

 

[Boller]

Is the text in red via ConditionalFormatting?

 

[charllie]

Hi Boller,

Thanks for your response.

No, the text is change to red manually using the font colour tool bar etc. Conditional formatting is not used on sheet 1 or sheet 2.

Black and red text are the only two colours used on sheet 1.

Hope that helps.

Thanks

 

[Boller]

ActiveWorkbook.Names.Add Name:="col", RefersToR1C1:="=GET.CELL(24,INDIRECT(""rc[1]"",0))"
Columns(1).Insert
With Range([A1], [B65536].End(xlUp)(1, 0))
    .FormulaR1C1 = "=IF(col,""d"",1)"
    .EntireRow.Sort Key1:=[A1], Order1:=xlAscending, Header:=xlNo
    On Error Resume Next
    .SpecialCells(xlCellTypeFormulas, 2).Offset(0, 1).Resize(, 255).Cut _
        Sheets("Sheet2").[A65536].End(xlUp)(2)
    On Error GoTo 0
    .EntireColumn.Delete
End With
ActiveWorkbook.Names("col").Delete

 

[charllie]

Hi Boller,

Thank you very much i really appreciate that. it does the job brilliantly.

The only error i got was if the macro was used by accident when there was no red text on page i got a default message.

Is there a way around that?

Thanks

 

[Boller]

Yes, I amended the code to handle that - must have amended it after you copied the code but before you responded.