change colour of picture based on cell value

[mickyflash]

hello again guys

I have found this video of Bills that explains how I could dynamically change the .width & .Height of a shape in excel, but I am struggling to convert this to the shapes colour?

There would be only 2 options, if the linked cell is = to TRUE to view the picture In colour, or greyscale if FALSE

Thanks in advance for your help

 

[Yongle]

Please create a NEW workbook and put the code below in a module in that workbook
- I want to eliminate risk of any other history or code getting in the way

Place links in A2:A6 and TRUE/FALSE in B2:B6

Run the code and let me know
thanks

Public Sub Add_Images_To_Cells()
   
    Dim lastRow As Long
    Dim URLs As Range, URL As Range, shpCel As Range
    Dim shp As Shape, N As String
   
    With ActiveSheet
        .Pictures.Delete
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set URLs = .Range("A2:A" & lastRow)

        For Each URL In URLs
            URL.Offset(0, 4).Select
            On Error Resume Next
            URL.Parent.Pictures.Insert URL.Value
            DoEvents
            Set shp = .Shapes(.Shapes.Count)
            If Not URL.Offset(, 1) Then shp.PictureFormat.ColorType = 2
        Next URL
    End With

     MsgBox "completed " & Now
End Sub

 

[mickyflash]

This Works!

Thank you so much! your endeavour to solve this for me is very very impressive

 

[Yongle]

OK - we now need to do the same thing in your original workbook

- delete the previous code in the original workbook and put the new code there in its place
- run and let me know

 

[mickyflash]

working like a charm

 

[Yongle]

Excellent. Are we done?

 

[mickyflash]

Excellent. Are we done?

Yes all done and working! Thank you so much

 

[Louis_Guionneau]

Yongle's code is pretty helpful. Nice job man!