How Do I Designate a Column in My Macro

[samsilverman]

I am using the following 2 macros to change the font color from Black to White before printing and then back again once I save the file. I never could get Lenze's solution from Feb 2002 to work. Just for the record, Lenze has helped me plenty (directly and indirectly), so I'm not hating. Can somebody help me code it for column A, instead of cell A1? Thanks. The macros are below. I'm also open to a better solution. Private Sub Workbook_BeforePrint(Cancel As Boolean) Range("A1").Select Selection.Font.ColorIndex = 2 End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Range("A1").Select Selection.Font.ColorIndex = xlAutomatic End Sub

 

[wolverineb]

To simply answer your question without wondering too much about it ...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Worksheets("Sheet1").Range("A:A").Font.ColorIndex = 2
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Sheet1").Range("A:A").Font.ColorIndex = xlAutomatic
End Sub

 

[samsilverman]

Thank you very much. I try it in the morning. peace.

 

[samsilverman]

The solution provided by wolverineb doesn't work in my workbook. What I have...

Private Sub Workbook_BeforePrint(cancel As Boolean)
Range("A1").Select
Selection.Font.ColorIndex = 2
End Sub

Private Sub Workbook_BeforeClose(cancel As Boolean)
Range("A1").Select
Selection.Font.ColorIndex = xlAutomatic
End Sub

...works great for cell A1. But I can't seem to figure out how to expand it to all of column A. If I substitute "A:A" for "A1", it applies the macros to columns A through M (which to me makes no sense at all).

Using the code provided by wolverineb led to an error message:

Runtime error '9':
Subscript out of range

Can anybody help? Thanks.

 

[Norie]

Don't use Select/Selection.

Did you actually try the code wolverine posted without changing it?

As far as I can see it should work fine as it is.

The only thing you might need to change would be the worksheet name.

If you don't have a worksheet called 'Sheet1' that's probably why you are getting the error you describe.

 

[VoG]

Do you have merged cells? If so try unmerging and use Center Across Selection Instead. This will help with that http://www.mrexcel.com/forum/showthread.php?t=335123

 

[wolverineb]

Your error really doesn't make sense, unless maybe it is the merged cell thing, but ...

You might also try the alternative way to reference columns:

Worksheets("Sheet1").Columns(1).Font.ColorIndex = 2

I would be worried that you are not telling Excel which worksheet you want to use in your Workbook level macros. WHICH Range("A1") do you want, for example? It's almost always a good idea to use the worksheet name when talking about a Range or Column or whatever.

 

[samsilverman]

OK, you helped me figure it out. I had used the "sheet#" designation instead of the name I had given it ("account"). Since both are in the VBE, I figured it was fine. Thanks for helping a rookie. Seems I tried every other solution than that simple one. btw, thanks to wolverineb as well!!!