Excel Formula to type in numeric value of green cells in the same row

Question

I would like some help in entering a formula in excel VBA I assume to enter all numbers of a certain highlighted color (green in this case) in a different column but is in the same row within a column range.

Excel Table 1

To help make things clearer, I uploaded an image above. The table in that image illustrates my desired results in the rightmost coloumn. I would also like it if its possible to display two or more cell values of the same color.

Green color details: http://www.htmlcsscolor.com/hex/00B050

Any help will be appreciated and please ask me any queries you need. Thank you.

EDIT: If its possible, can I get a code that shows two or more green cell values in separate columns instead of being shown together with a + sign?

Answer 1

it seems like your data are in a Excel Table

assuming it's so and that table name is "MyTable" you can try this code:

Option Explicit

Sub main()
    Dim row As ListRow
    Dim icol As Long
    Dim formula As String

    For Each row In ActiveSheet.ListObjects("MyTable").ListRows
        formula = ""
        For icol = 1 To row.Range.Count - 1
            With row.Range(1, icol)
                If .Interior.Color = RGB(0, 176, 80) Then formula = formula & .value & "+" '.Address(False, False)
            End With
        Next icol
        If formula <> "" Then row.Range(1, icol).value = Left(formula, Len(formula) - 1)
    Next row
End Sub