How to conditionally format Font Typeface?

[GB_in_Sydney]

Hello All,

This is my first thread posted, but have used the site as a great resource many times before.

My Problem: How to conditionally change the font name (typeface) based on the value in the cell.

Partial Solution: I have used the Worksheet_Calculate Event to successfully change the fontname as follows. It works but negates any 'undo' function available to the user.

Please Login or Register  to view this content.

My Question: Is there a neater way that conditionally changes the font name that also retains the undo functionality for the user?

All help is greatly appreciated!

GB.

[GB_in_Sydney]

I changed the code so that it only changes the font name when required (see below). This means that 90% of the time the code does nothing and therefore the 'undo' function is available. Any ideas for the other 10% of the time when it actually changes the font name?

Please Login or Register  to view this content.

Thanks, GB.

[dominicb]

Good morning GB_in_Sydney

Unfortunately, conditional formatting won't change a cells font - only the font style. And as regular formulae won't change the style or font of a cell this means that to find a workaround you need to resort to VBA. As you know whenever a macro is run your undo stack is cleared, and there is no workaround for this - many have tried and failed to attempt to locate and manipulate via VBA the undo stack, which resides in memory, but unfortunately it appears to float around from one session to the next, and so far nobody has come up with an acceptable solution.

DominicB