Set Print Area

[Katie Hoys]

Hi

I have a workbook with lots of worksheets

How can I do the following ........

Set the print area of every worksheet in the work book to A1:A10

Enter the same text in to Cell A1

And change the colour of Cell A2 to red

I only know a little VB so please keep it simple


Thanks

Katie

 

[Paul B]

Katie, here is one way

Sub test()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1").FormulaR1C1 = "This Is A1"
ws.Range("A2").Interior.ColorIndex = 3
ws.PageSetup.PrintArea = "$A$1:$A$10"
Next ws
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Katie Hoys]

Katie, here is one way

Sub test()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1").FormulaR1C1 = "This Is A1"
ws.Range("A2").Interior.ColorIndex = 3
ws.PageSetup.PrintArea = "$A$1:$A$10"
Next ws
Application.ScreenUpdating = True
End Sub

Thankyou !

Works a treat

 

[Katie Hoys]

Katie, here is one way

Sub test()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Range("A1").FormulaR1C1 = "This Is A1"
ws.Range("A2").Interior.ColorIndex = 3
ws.PageSetup.PrintArea = "$A$1:$A$10"
Next ws
Application.ScreenUpdating = True
End Sub

What if I want to change the color to 'no fill' ?

Is there an associated number to use like Colorindex = 3 equals Red

No fill = ???????

 

[Paul B]

Katie, use this, Interior.ColorIndex = xlNone

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Gord Dibben]

Katie

No fill colorindex = -4142

Note the minus(-) sign.

Gord Dibben Excel MVP

 

[Katie Hoys]

Thanks for all your help Paul and Gord