How Do I Designate a Column in My Macro

samsilverman

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

Well-known Member
Joined
Jan 26, 2005
Messages
690
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
 

samsilverman

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
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.
 

wolverineb

Well-known Member
Joined
Jan 26, 2005
Messages
690
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

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

Forum statistics

Threads
1,082,323
Messages
5,364,589
Members
400,808
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top