Set Print Area
 

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

Set Print Area
 

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" wrote in message
...
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

Set Print Area
 

"Paul B" wrote in message
...
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

Set Print Area
 

"Paul B" wrote in message
...
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 = ???????

Set Print Area
 

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

"Katie Hoys" wrote in message
...

"Paul B" wrote in message
...
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 = ???????

Set Print Area
 

Katie

No fill colorindex = -4142

Note the minus(-) sign.

Gord Dibben Excel MVP

On Sun, 12 Dec 2004 00:11:37 GMT, "Katie Hoys" wrote:


"Paul B" wrote in message
...
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 = ???????

Set Print Area
 

Thanks for all your help Paul and Gord

:-)