Detect bold text in MS Excel

November 2, 2008 2:36 PM

I have a very large MS Excel spreadsheet in Mac Office 2008 (yuk). I want to find every cell that contains bold text and append an asterisk to it -- i.e. "bold cell text" becomes "bold cell text*". I can probably find a Windows machine if necessary. How can this be done automatically?
posted by beniamino to Computers & Internet (4 answers total)

The following macro will do it, though it'll take a while, as it considers every cell on the sheet.
If you know the extent of your spreadsheet, you can change "Cells" to "Range("A1:Z10000") or something to speed it up.

Sub MarkBold()

For Each c In Cells
If c.Font.Bold = True Then c.Value = c.Value & "*"
Next c

End Sub

...alternatively - if you also want to de-boldify as per your example text, use:

Sub MarkBold()

For Each c In Cells
If c.Font.Bold = True Then
c.Value = c.Value & "*"
c.Font.Bold = False
End If

Next c

End Sub

posted by pompomtom at 3:01 PM on November 2, 2008

Perfect! Thanks pompomtom, you just saved me hours of typing.
posted by beniamino at 3:14 PM on November 2, 2008

You should change "Cells" to "UsedRange" so it only looks at cells that are actually used.

Excel maintains a hidden "used range". You can tell its extent if you press CTRL+END, which takes you to the lower-right corner of the used range.
posted by zixyer at 3:34 PM on November 2, 2008

And one tiny, tiny nit, which I'm only going to pick because I see it all over the place and I've become allergic to it:

Testing a Boolean value for equality with True is redundant. Instead of

If c.Font.Bold = True Then

you can simply use

If c.Font.Bold Then

posted by flabdablet at 7:00 PM on November 2, 2008