How to set font properties to an entire workbook?

[Hamish]

I would like to set the font colour of my workbook in its' entirety. How do I do this?

 

[Smallman]

Hi Hamish

To do this quickly. Click on the left most sheet. Now hold shift and click on the right most sheet. All sheets should appear White.

Now Press Ctrl A. Choose the font colour you want.

All sheets will show the font colour you just chose.

Take care

Smallman

 

[Hamish]

Thanks Smallman. Is there a way to do it in VBA?

 

[Smallman]

Hi Hamish

For a Vb solution something like this should see you over the line.

Sub ColourMeBad()
Dim ws As Worksheet
 
    For Each ws In Sheets
        ws.Cells.Font.ColorIndex = xlAutomatic
    Next ws
End Sub

Take care

Smallman

 

[Hamish]

Hi Smallman,

The code is returning error: "subscript out of range"

Is there another way I can refer to the workbook/worksheets?

 

[Smallman]

Hamish

Do you have hidden worksheets? Do you have merged cells? I am just guessing here.

How about you open a fresh workbook and run my coding. It runs well on a test workbook. Now all you have to decide is what is different from your workbook to the test one. If you can not figure out the difference post your workbook.

Take care

Smallman

 

[Analyst]

you can also record a macro while doing smallman's first suggestion.

 

[Hamish]

It's the curse of the dreaded merged cells!

Thanks

 

[Smallman]

Hi Hamish

Thanks for posting back with your feedback. Now anyone following the thread with the same issue will have to tools to solve the problem.

Take care

Smallman