Replace all pictures in a sheet with text OR specific formatting

[nick_jco]

I get these crosswords online and paste them into Excel. The black spaces of the crossword come from the website as little pictures in each cell.
I need a way to replace all the picture in the sheet with specific formatting (black fill), or specific text (then I could use regular Conditional Formatting).
Example sheet is attached with a before and after.
Any help would be appreciated!

 

[BobBridges]

Replacing the picture with a black fill shouldn't be hard, but I'm pretty sure you'd have to write an Excel program (in the VBA language) to do that. I recommend asking the question again in the "VBA Macros" forum.

But filling in with text ... you're talking about the numbers in a crossword puzzle that indicate "24 across" and "3 down" etc, right? How are you going to get Excel to display two numbers in one cell? Because it seems to me if you can't figure out how to do that manually, you won't be able to get an Excel program to do it either. Isn't that a show-stopper?

 

[BobBridges]

Wait, I just saw it: You can use superscripts. You can do "23 down" and "3 across" in the same cell by displaying "23³" (the last char in superscript), except you'd want to insert a space and mess with the sizes. That might work, if you make the cell sizes big enough. Again, for that you want to write a program in VBA.

 

[Marc L]

Any help would be appreciated!

According to your attachment a VBA demonstration to paste only to the 'before' worksheet module :​

Sub Demo1()
    For Each Pic In Pictures
             Pic.TopLeftCell.Interior.ColorIndex = 1
             Pic.Delete
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[nick_jco]

According to your attachment a VBA demonstration to paste only to the 'before' worksheet module :​

Sub Demo1()
    For Each Pic In Pictures
             Pic.TopLeftCell.Interior.ColorIndex = 1
             Pic.Delete
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Hi Marc, thanks for this response!
I pasted your code into VBA and hit execute and get this error:

NOTE: I have never actually used VBA before so I'm really out of my element here.

 

[YasserKhalil]

It is working at my side. You have to put Marc's code in the worksheet module. If you prefer the standard module change it a little

Sub Demo1()
    Dim Pic As Picture
    Application.ScreenUpdating = False
        For Each Pic In ActiveSheet.Pictures
            Pic.TopLeftCell.Interior.ColorIndex = 1
            Pic.Delete
        Next Pic
    Application.ScreenUpdating = True
End Sub

 

[Marc L]

You have to put Marc's code in the worksheet module.

Yes, the classic bad readers' trap …​

​

 

[nick_jco]

It is working at my side. You have to put Marc's code in the worksheet module. If you prefer the standard module change it a little

Sub Demo1()
    Dim Pic As Picture
    Application.ScreenUpdating = False
        For Each Pic In ActiveSheet.Pictures
            Pic.TopLeftCell.Interior.ColorIndex = 1
            Pic.Delete
        Next Pic
    Application.ScreenUpdating = True
End Sub

Thanks @YasserKhalil this code worked for me!

 

[YasserKhalil]

@nick_jco Thanks goes to Mr. Marc. This is his code. I just guided you.