Find and copy entire column based on font color into another sheet

  • Hello,
    I would appreciate if any kind soul could help me. It's stupid question but I'm new to VBA and so far I tried every help I was able to find, but they didn't work for me (or I modified them poorly).
    I have two sheets named "orders" and customers". There are lots of numbers on sheet "orders" but only in one column contains the cells I need. They are written in different font colour (-4165632 some shade of blue) and are somewhere between columns A-Z, rows 1-300. I need these orders copied into yellow (15466239) cells, in column N, on sheet "customers". The orders are in right order (like first number should be put into first yellow cell) and I have more orders then yellow cell. My idea was to write a macro which would copy the orders to sheet "customers", column T, starting T1 (blank column) and then distribute it to yellow cells. The second part works for me nicely, but I wasn't able to copy them using a macro and I'm starting to be frustrated. I wanted something easy, tried lots of variation, changed the range,... but nothing worked. There's no error message, it just does nothing. Could you please help me?


    [VBA]


    Sheets("orders").Activate


    Set MR = Range("A10:Z10")
    For Each Cell In MR
    If Cells.Font.Color = -4165632 Then
    Cell.EntireColumn.Copy
    Sheets("customers").Activate
    Range("T1").PasteSpecial xlFormats
    End If
    Next


    [/VBA]

  • Adelaine,


    I haven't been able to find any reference to excel font colors like you have shown above how are you getting the font color? You can use the below code to get the font color number then use that number in your if statement changing your if to if Cell.Font.ColorIndex = "Color index from below" Then


    Code
    1. Function GetFontColor(ByVal Target As Range) As Integer
    2. GetFontColor = Target.Font.ColorIndex End Function

    in an empty cell add the formula =GetFontColor(Cell with blue color you need) hopefully this will help you.

  • Hi bigfoot,
    Thank for the your reply. I got it by recording macro automatically. Your function gets me number colour -4142 (fascinating, because it should be standard blue). But I already had tried to change font colour to red (3) to be sure it's not because of the wrong number of colour. To add to if statement "Index" after color and change the number of colour to -4142 or 5 didn't help.