3

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

purple struck through text followed by regular green text

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.

RubberDuck
  • 11,933
  • 4
  • 50
  • 95
user2619930
  • 113
  • 1
  • 5
  • 1
    You need to examine the contents of the cell character by character, test each for the format properties of interest, and retain unique combinations only. – Gene Skuratovsky Dec 29 '14 at 17:28
  • Once you've developed some code in accord with Gene's comment, post it here and we'll help you troubleshoot it. – Ron Rosenfeld Dec 29 '14 at 17:34
  • @GeneSkuratovsky How do you examine character by character? I only know how to get the contents without the formatting. – Cody Piersall Dec 29 '14 at 19:22
  • @GeneSkuratovsky Never mind! Looks like that question [already has an answer](http://stackoverflow.com/a/7618187/1612701) – Cody Piersall Dec 29 '14 at 19:24
  • @Cody Yes, it is an answer regarding 'char-by-char'... However, it shows how to check for a specific color; it does not show how to generally determine which color is used to format the character... OP might also want to examine such formating properties as font name, font size, bold, italic, superscript, etc. As long as we are talking "about a cell in Excel" we should not dismiss cell alignment (vert and horiz), fill color and pattern, gradient, borders (which, line size and color), etc. The question, as showing now, is still a very-very wide question. – Gene Skuratovsky Dec 29 '14 at 20:15

1 Answers1

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

nwhaught
  • 1,562
  • 1
  • 15
  • 36