Change shape color by row to specified color

[GeeWhiz7]

Hi folks,
I have a simplified timeline example in the attached image below to illustrate what I am trying to do. For some reason XL2bb isn't working for me to put the mini-sheet in.

• With a macro, I would like to recolor any shape that is in a row marked with an "assign color" (C2:C6) entry.
• I already can manage the RGB color using this snippet so mainly I'm trying to figure out the syntax to loop through the rows and shapes.

        Set s = ws.Shapes.AddShape(Type:=msoShapeRectangle, _
        Left:=c.Left, _
        Top:=c.Top, _
        Width:=c.Width + Dur, _
        Height:=c.Height)
    
        s.Fill.ForeColor.RGB = RGB(R, G, B)

I've been trying to work out the code, but can't get things to work, for example For Each can't loop through my rows so I'm not sure how else to loop through each row, then each shape.

Sub ReColorShape()
    Dim nRow As Long
    
    Dim R, G, B As Integer
    
    R = 255
    G = 145
    B = 192
    
    With ActiveSheet
        nRow = .Cells.SpecialCells(xlCellTypeLastCell).Row 'throws error about only being able to loop over collection or group object
        
        Do Until nRow = 1
            
            For Each shp In ActiveCell.Row
            
                Set s = ws.Shapes.ActiveShape 'or should it be =ActiveSheet.Shapes ?
                
                  s.Fill.ForeColor.RGB = RGB(R, G, B)
                  
            Next shp
            
            nRow = nRow - 1
            
        Loop
    End With
End Sub

 

[EXCEL MAX]

You can try this one...

Sub ColorizeShapes()

   Dim vShape As Shape
   Dim vSTLC
   Dim vColor, vRed As Integer, vGreen As Integer, vBlue As Integer

   For Each vShape In ActiveSheet.Shapes
      vSTLC = vShape.TopLeftCell.Address
      vColor = Cells(Range(vSTLC).Row, 2).Interior.Color
      vRed = vColor And 255
      vGreen = vColor \ 256 And 255
      vBlue = vColor \ 256 ^ 2 And 255
      vShape.Fill.ForeColor.RGB = RGB(vRed, vGreen, vBlue)
   Next vShape

End Sub

 

[GeeWhiz7]

You can try this one...

Sub ColorizeShapes()

   Dim vShape As Shape
   Dim vSTLC
   Dim vColor, vRed As Integer, vGreen As Integer, vBlue As Integer

   For Each vShape In ActiveSheet.Shapes
      vSTLC = vShape.TopLeftCell.Address
      vColor = Cells(Range(vSTLC).Row, 2).Interior.Color
      vRed = vColor And 255
      vGreen = vColor \ 256 And 255
      vBlue = vColor \ 256 ^ 2 And 255
      vShape.Fill.ForeColor.RGB = RGB(vRed, vGreen, vBlue)
   Next vShape

End Sub

Hi Excel Max. I've been trying to figure out how to do this for a while now and your solution works nicely. The main parts that I could not figure out are using .TopLeftCell.Address to get the row # the shape (didn't know you could do that) is in and then using that to help assign vColor the color out of the row # and column #2. This already will help me with another idea I'm working on. Thanks!!

 

[EXCEL MAX]

I'm glad that you found this as helpful.
So, you see we can loop between shapes instead rows.
Always look in two ways.
TopLeftCell property is basics. Converting colors is trick.

 

[GeeWhiz7]

I'm glad that you found this as helpful.
So, you see we can loop between shapes instead rows.
Always look in two ways.
TopLeftCell property is basics. Converting colors is trick.

Hi Excel Max, I didn't get a chance to reply earlier, but wanted to ask a question about what you thought a good resource to learn dealing with shapes would be for example? Just trying something new for example like to select a shape and change it from one type (rectangle) to another (star), but all the docs.microsoft stuff is hard for me to get through as pretty inexperienced with vba. I think .pickup of another shape as a template and .apply might work if there is no other way to replace, but maybe there is a site out there that walks through these things?

 

[EXCEL MAX]

I don't know any special site that may teach you about shapes.
The best place I know for learning is here.
Dealing with shapes is rare in VBA coding, and hard even for older members of this forum,
so the good practices is look to VBA Object Browser and Microsoft Documents.
To explore shapes deeply, you can send a specific problem to this forum.
Don't be lazy describing goal you want to achive.
I belive, here is enough members that are interested to research shapes and share knowelage with you.

 

[GeeWhiz7]

I don't know any special site that may teach you about shapes.
The best place I know for learning is here.
Dealing with shapes is rare in VBA coding, and hard even for older members of this forum,
so the good practices is look to VBA Object Browser and Microsoft Documents.
To explore shapes deeply, you can send a specific problem to this forum.
Don't be lazy describing goal you want to achive.
I belive, here is enough members that are interested to research shapes and share knowelage with you.

makes sense and must be why I keep coming back to this forum. Thanks for your help and I will keep progressing.