-1

I have just discovered VBA while trying to find a solution for the following scenario:

I have an Excel workbook with the following table:

enter image description here

And I'm trying to create a graph, showing each team's score in comparison. I want the graph to copy the cell's colors too, but this is not possible because the cells in column A, containing the Teams' names, have no fill.

What I'm trying to do, is to have a VBA code which automatically copies the color from B1 to A1, from B2 to A2, from B3 to A3, etc. I have found several articles, with codes, but none of them worked, because the cells in column B are filled with a certain color based on Conditional Formatting.

Also, since I'm very new to this, I will need you to explain how to perform a certain action related to VBA.

braX
  • 11,506
  • 5
  • 20
  • 33
Andrei Matiș
  • 1
  • 1
    Hi. Here's one example of how to get the conditional formatting colour: https://stackoverflow.com/questions/45122782/how-to-get-the-background-color-from-a-conditional-formatting-in-excel-using-vba – Joe Mar 19 '21 at 12:00

1 Answers1

0

The color dependent on a conditional formatting can be got via Range.DisplayFormat property.

So your requirement could be fulfilled using following code:

Sub copyConditionalColors()
 Dim r As Range
 Dim c As Range
 Set r = ActiveSheet.Range("B1:B5")
 For Each c In r
  c.Offset(0, -1).Interior.Color = c.DisplayFormat.Interior.Color
 Next

End Sub
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • The chart doesn't seem to copy the colors from the cells in Column A. Do you have any idea why? I'm sorry, but I'm really new to this. – Andrei Matiș Mar 19 '21 at 12:05
  • @Andrei Matiș: What chart are you talking about? – Axel Richter Mar 19 '21 at 12:06
  • I created this chart to represent the table: https://imgur.com/8lbMu8s And I'm trying to make it copy the colors from the cells in column A. – Andrei Matiș Mar 19 '21 at 12:12
  • I have found this https://www.extendoffice.com/documents/excel/4837-excel-color-chart-based-on-cell-color.html. It worked! Thank you so much for your help, you're a God! :D – Andrei Matiș Mar 19 '21 at 12:19