I have an org chart which is generated from an Excel table. In the table and chart we have a field “Source” to show if the role is filled internally or externally.

I would like to be able to run a macro which then colours the internal and external resources different colours. I would also like to be able to shade the current vacancies (name = vacant) a lighter colour.

I have the following code which makes the changes to the shapes when you look at the shape sheets (below). However the actual shape doesn’t change colour, even when I manually mess around with the contents of the shape sheet 🙁 I am so confused.

Sub ReColour()
Dim shp As Visio.Shape
Dim pagshape As Visio.Shape
Set pagshape = Visio.ActivePage.PageSheet

For Each shp In Visio.ActivePage.Shapes
    If shp.CellExistsU("prop.source", visExistsAnywhere) <> 0 Then 'Debug.Print shp.CellsU("prop.source").ResultStr(visNone)
        If shp.CellsU("prop.Source").ResultStr(visNone) = "HO - Full time (back filled)" Then
            shp.CellsU("Fillforegnd").FormulaU = "=THEMEGUARD(RGB(250,100,50))"
            shp.CellsU("FillBkgnd").FormulaU = "=THEMEGUARD(RGB(0,100,150))"
            shp.CellsU("Fillpattern").FormulaU = "27"
            Debug.Print shp.ID
        End If
    End If
    
Next shp

End Sub

One thing I did spot was that the shape sheet has a field user.backFillColor which is set to sheet.25!FillForegnd where 25 is the ID of the shape. This isn’t the same as =fillForegnd and I am very confused as to where sheet25! is pointing to as another shape (ID 155) on the same sheet is pointing to sheet155! so it doesn’t appear to be a different sheet 🙁

any help will be gratefully received as this feels like something that should be easy but the shape are just behaving very strangely.