(Solved) – Auto-format Cell Based on Value with VBA


I’m developing a database querying tool for my team and have had a small hiccup which I haven’t been able to solve. This one’s been bugging me for a while!

Problem: text of interest returned from the database is in Rich Text Format (with formatting tags). I’d like to have it either formatted (ideally) or in just plain text (not ideal). I know that Excel is capable of doing this, because all I have to do is click into the cell, and it formats automatically. I want to achieve this auto-formatting on the back end through VBA.

Example of queried text:

{rtf1ansideff0{fonttbl{f0fnilfcharset0 Tahoma;}}

{*generator Msftedit;}viewkind4uc1pardlang1033f0fs16 Some text will be right here.par   


Record Macro when I click into the cell: this does not provide a general solution. It shows all the formatting that was done, but this is specific to the cell.

ActiveCell.FormulaR1C1 = "Some text will be right here." & Chr(10) & "  "

This is not helpful to me.

I’ve tried a few things I’ve seen on the internet; none of which have worked. Specifically, selecting the cell I’d like to have formatted and throwing keystrokes (F2, Enter), Calculating cells, and maybe another solution or two.

What do y’all think?

Leave a Reply

Your email address will not be published. Required fields are marked *