-1

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?

Yousef Alkaff
  • 3
  • 2

1 Answers1

0

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
user3598756
  • 28,893
  • 4
  • 18
  • 28
  • I'm sorry, I am not an excel expert but should I run this as a macro? It's not working when i run the macro on the selected cell. Please advise. Thanks. – Yousef Alkaff Sep 26 '16 at 06:22
  • what does "It's not working" mean? Please both _how_ are you trying to use this code _and_ specify what happens (what error messages, referencing what ...) – user3598756 Sep 26 '16 at 06:25
  • I added a new column and ran the macro at the topmost cell of that column with the green highlighted cells at the left of that column. Nothing happens after running the macro. no error messages or anything like that. I did rename my table to "MyTable" under the design table tab. – Yousef Alkaff Sep 26 '16 at 06:44
  • Where did you add a new column? what do you mean by "ran the macro at the topmost cell of that column"? Are your highlighted cells colored with RGB(0, 176, 80)? Are you running the macro with active sheet being the one where the relevant table is. You may want to add images of what your actual table looks like before "adding a new column" and after – user3598756 Sep 26 '16 at 07:00
  • it worked perfectly! I'm so sorry for the trouble caused. I was working in a table set from right to left set in the arabic language and it didn't display anything. When I converted it to english, It worked like a charm. Thank you so much my friend. – Yousef Alkaff Sep 26 '16 at 07:23
  • as per this site rule, you have to post a new question with code you have tried – user3598756 Sep 26 '16 at 10:05
  • Sorry about that. Just posted my question. – Yousef Alkaff Sep 26 '16 at 10:21