Excel FAQs: Font formats

Thanks for the interesting article.

Can anyone show me how to change the default text to red, for example, so that I can add text to an existing spreadsheet in various cells without having to change back to red each time I move cell.  I know I can do them all in one go at the end by selecting all the cells but then I have to remember where they all are.  Excel version 2007.

Thanks.

Very often styles multiply when cells are copied & pasted from other workbooks or other instances of Excel. If you wait till you get the error message "too many cell formats", it's too late, you have over 4000 different cell formats. I've seen workbooks with over 60,000 styles, but not all were in use.

 It may be useful to have a macro that checks for too many styles (eg over 100) and deletes the unused ones. This code

Sub CleanStyles()

Dim sty As Style, wbTemp As Workbook

' First, remove all styles other than Excel's own.

' they may have arrived from pasting from other workbooks

For Each sty In ThisWorkbook.Styles

   If Not sty.BuiltIn Then sty.Delete

Next

'Second, revert the remaining styles to Excel's default for a new workbook

Set wbTemp = Workbooks.Add

Application.DisplayAlerts = False

ThisWorkbook.Styles.Merge wbTemp

wbTemp.Close SaveChanges:=False

Application.DisplayAlerts = True

End Sub

Another idea is to check whether the Numberformat of the Normal style is not General. I have seen workbook style corruption where it acquires a Date format.

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/excel-2007-spontaneously-formats-entire-work-book/1ae533af-ecee-41fa-866a-2590343a111d

if activeworkbook.styles("Normal").NumberFormat <> "General" then show some message and offer to reset it to General

If you're going to click in each cell to enter the text, you may as well do one more click on the Font colour button, change it to red once, and then it stays at red for any subsequent click. Just one more click per cell. Or Ctrl+Y if you prefer to use the keyboard.

BTW :

If you change the Normal style, that changes the display of all cells.

If you define a new style, you still have to remember to apply it, so that's still a click,

Ctrl+Y is a help as I don't have to reach for the mouse and can navigate around using the keyboard.

I know it wasn't the main thrust of the article, but discussion of inserting ticks and comments about doing things repeatedly got me thinking.

Below is a little macro I recorded to insert a tick from the wingdings font into the current cell - nothing clever about it, just to show it can be done..

What I also wanted to point out is that you can associate this macro with a button on the Quick Access Toolbar (2007 onwards) AND despite the many deficiencies of the QAT, you can tell it during the customisation that the button is for the current workbook only - meaning that it is to some extent context sensitive. To do this, right-click on the QAT, select customise... and then in the dropdown at the top right of the dialogue select the name of the current workbook (rather than "for all documents (default)".

In other words you can create a QAT button within a re-used spreadsheet (or template?) to insert a tick or other symbol with one mouse click.

Hope that helps.

Sub InsertTick()
' InsertTick Macro
'
    ActiveCell.FormulaR1C1 = "ü"
    With ActiveCell.Characters(Start:=1, Length:=1).Font
        .Name = "Wingdings"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
End Sub