How do I return all the formatting from a cell in Excel using VBA?

Question

How can I use VBA to return all of the formatting information about a cell in Excel. For example:

I need to know which text is purple, which is green, and which is struck through. Range("B2").Value only returns the plain text. I need the formatting as well.

Answer 1

your best bet is probably to use the Intellisense to explore all of the values that are available to you. You can finish off the code snippet below with things like .Color, .Strikethrough, .Bold, etc, and have them print to the immediate window. (Ctrl+G displays the immediate window if you don't currently have it displayed)

Sub test()
debug.Print Range("B1").Font 'finish this line with any of the IntelliSense options to learn things about the text
End Sub

You can also take a look at the properties listed on the MDSN site for the font object