Macro to Format Text

I create sales quotes of nursery plants in Excel. The plant names are listed in two ways, scientific botanical name and common name. For example, Liquidambar styraciflua, Sweetgum. Because of limited physical space on my sheet, the scientific and common name are typed in the same cell always separated by a comma, instead of keeping them in separate columns. And here is the kicker, I like to bold and italicize the scientific name but not the common name.

This leaves me with a time consuming task after typing the text of highlighting the scientific name within each cell, and then clicking the bold and italic buttons. Doing this for one or two cells is fine, but I have hundreds. I would like to develop a macro that would examine the string of text in the current selected cell, locate the comma and then bold and italicize the text to the left of the comma. The scientific names of the 300 different plants that we sell are all different quantites of characters, so simply counting characters from the beginning is not useful. To me, the comma is the key to programming what gets bolded and italicized to the left of it, and unformatted to the right of it.

Increasing the capability of this macro would include running it on a selected rangs of cells.

Can anyone help?

A few approaches in truth - one might be:

Please Login or Register  to view this content.

the above assumes of course the cells in question contain constants (not formulae)

edit: it's not clear if you want to include a reverse in the above (ie set bold & italic to false where comma not found - ie to purge legacy formats)

Originally Posted by dbrodak

I notice that an undo will not undo beyond the running of the macro. Is that normal?

Yes, normal.