Retrieve Color based columns from Excel

Hello,

I have an excel file with random color in columns header.
How to extract all the column data which has green color as the columns header.

Thank you in advance for any help you can provide.

Regards,
Geet M

@iamgeet ,
You can try this with Macro, you can run the macro using the excel application scope and run the vbcode. The below code will check the header cell background color is green or not. If it is green then the column values will be copied to a new sheet.

Sub test()
Sheets("sheet2").Activate
Dim intColIndex As Integer
'To check only headers
Set MR = Range("A1:Z1")

For Each cell In MR
    'To Check Green is the Background color of the cell or not
    If cell.Interior.Color = 52224 Then
        cell.EntireColumn.Copy
        Sheets("Sheet1").Activate
        intColIndex = intColIndex + 1
        'To append the values in the Next Column
        Range(Columns(intColIndex).Address).PasteSpecial xlPasteAll
    End If
Next
End Sub
2 Likes

Hello,

Please if possible can send the file where you have execute the code.

@iamgeet ,

If you are looking for the excel then download it now.
Excel_Color.xlsx (11.3 KB)

Hello,

The code is working for the excel file which you shared.

Unfortunately, its not working for the excel which I have.
TestingColor.xlsx (9.0 KB)

@iamgeet ,

Compare the background color of my excel with yours. Looks different.

In the code I am checking and comparing the cell background color with 52224, instead of that for your color change the value as 5296274. Then it will work for your excel as well.

Sub test()
Sheets("sheet2").Activate
Dim intColIndex As Integer
'To check only headers
Set MR = Range("A1:Z1")

For Each cell In MR
    'To Check Green is the Background color of the cell or not
    If cell.Interior.Color = 5296274 Then
        cell.EntireColumn.Copy
        Sheets("Sheet1").Activate
        intColIndex = intColIndex + 1
        'To append the values in the Next Column
        Range(Columns(intColIndex).Address).PasteSpecial xlPasteAll
    End If
Next
End Sub
1 Like

Yes, I just know got the background color number 5296274.

Hello,

Thank you so much.
Its working.

Regards,
Geet M

1 Like