How Do I Designate a Column in My Macro

samsilverman

Board Regular
Joined
Nov 9, 2009
Messages
176
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
To simply answer your question without wondering too much about it ...

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top