I have the following sub that will shade every other row in
a spreadsheet. It works fine if I run it manually via
alt-F8
----------------------------------------------------------------

Sub ShadeEveryOtherRow()

Range("A2").EntireRow.Select
Do While ActiveCell.Value < ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End Sub
----------------------------------------------------------------

But if I try to have it run at open, it does nothing. I
have tried placing the code directly in workbook_open,
nothing happens. I have tried calling the sub in
workbook_open,

----------------------------------------------------------------

Private Sub Workbook_Open()
ShadeEveryOtherRow
End Sub
----------------------------------------------------------------

and that doesn't work either. What the heck is going on
here!?

Thanks,
gm

gm

Unless EnableEvents is False or your Workbook_Open sub is the wrong place,
then I think it should work. Put a MsgBox in the workbook open and see if
it's even executed. If not, I'll bet it's in the wrong module. Make sure
it's in the ThisWorkbok module.

Generally, you don't need to Select objects in order to work with them, and
it's inefficient. You might consider changing that sub to

Dim i As Long

With ActiveSheet
For i = 2 to .Rows.Count Step 2
If IsEmpty(.Cells(i,1)) Then
Exit For
Else
.Cells(i,1).EntireRow.Interior.ColorIndex = 15
End If
Next i
End With

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


wrote:
I have the following sub that will shade every other row in
a spreadsheet. It works fine if I run it manually via
alt-F8
----------------------------------------------------------------

Sub ShadeEveryOtherRow()

Range("A2").EntireRow.Select
Do While ActiveCell.Value < ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End Sub
----------------------------------------------------------------

But if I try to have it run at open, it does nothing. I
have tried placing the code directly in workbook_open,
nothing happens. I have tried calling the sub in
workbook_open,

----------------------------------------------------------------

Private Sub Workbook_Open()
ShadeEveryOtherRow
End Sub
----------------------------------------------------------------

and that doesn't work either. What the heck is going on
here!?

Thanks,
gm

Thanks for the reply. I added the msgbox as you suggest, so now workbook open
looks like:
----------------------------------------------------------------
Private Sub Workbook_Open()
MsgBox ("Workbook open in progress")
ShadeEveryOtherRow
----------------------------------------------------------------
The message box happens, the other code still does not execute. I am assuming
that since the message box is working, then the EnableEvents you mention is not
False.

So can anyone else come up with another reason why this code will not execute
on workbook open?

thanks
gm

Dick Kusleika wrote:

gm

Unless EnableEvents is False or your Workbook_Open sub is the wrong place,
then I think it should work. Put a MsgBox in the workbook open and see if
it's even executed. If not, I'll bet it's in the wrong module. Make sure
it's in the ThisWorkbok module.

Generally, you don't need to Select objects in order to work with them, and
it's inefficient. You might consider changing that sub to

Dim i As Long

With ActiveSheet
For i = 2 to .Rows.Count Step 2
If IsEmpty(.Cells(i,1)) Then
Exit For
Else
.Cells(i,1).EntireRow.Interior.ColorIndex = 15
End If
Next i
End With

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

wrote:
I have the following sub that will shade every other row in
a spreadsheet. It works fine if I run it manually via
alt-F8
----------------------------------------------------------------

Sub ShadeEveryOtherRow()

Range("A2").EntireRow.Select
Do While ActiveCell.Value < ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End Sub
----------------------------------------------------------------

But if I try to have it run at open, it does nothing. I
have tried placing the code directly in workbook_open,
nothing happens. I have tried calling the sub in
workbook_open,

----------------------------------------------------------------

Private Sub Workbook_Open()
ShadeEveryOtherRow
End Sub
----------------------------------------------------------------

and that doesn't work either. What the heck is going on
here!?

Thanks,
gm

Is the correct sheet the active sheet? Or, is A2 not empty?

Perhaps a blank sheet is the activesheet and thus your code executes, but
does nothing.

Put the message box in the ShadeEveryOtherRow code at the top as the first
executable instruction.

--
Regards,
Tom Ogilvy

wrote in message ...
Thanks for the reply. I added the msgbox as you suggest, so now workbook
open
looks like:
----------------------------------------------------------------
Private Sub Workbook_Open()
MsgBox ("Workbook open in progress")
ShadeEveryOtherRow
----------------------------------------------------------------
The message box happens, the other code still does not execute. I am
assuming
that since the message box is working, then the EnableEvents you mention
is not
False.

So can anyone else come up with another reason why this code will not
execute
on workbook open?

thanks
gm

Dick Kusleika wrote:

gm

Unless EnableEvents is False or your Workbook_Open sub is the wrong
place,
then I think it should work. Put a MsgBox in the workbook open and see
if
it's even executed. If not, I'll bet it's in the wrong module. Make
sure
it's in the ThisWorkbok module.

Generally, you don't need to Select objects in order to work with them,
and
it's inefficient. You might consider changing that sub to

Dim i As Long

With ActiveSheet
For i = 2 to .Rows.Count Step 2
If IsEmpty(.Cells(i,1)) Then
Exit For
Else
.Cells(i,1).EntireRow.Interior.ColorIndex = 15
End If
Next i
End With

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

wrote:
I have the following sub that will shade every other row in
a spreadsheet. It works fine if I run it manually via
alt-F8
----------------------------------------------------------------

Sub ShadeEveryOtherRow()

Range("A2").EntireRow.Select
Do While ActiveCell.Value < ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End Sub
----------------------------------------------------------------

But if I try to have it run at open, it does nothing. I
have tried placing the code directly in workbook_open,
nothing happens. I have tried calling the sub in
workbook_open,

----------------------------------------------------------------

Private Sub Workbook_Open()
ShadeEveryOtherRow
End Sub
----------------------------------------------------------------

and that doesn't work either. What the heck is going on
here!?

Thanks,
gm

Unless you don't have the Workbook_Open() code in the ThisWorkbook
module, it should work fine.

However, instead of running a macro every time, why not select the
entire sheet and use the technique found he

http://cpearson.com/excel/banding.htm



In article , wrote:

I have the following sub that will shade every other row in
a spreadsheet. It works fine if I run it manually via
alt-F8
----------------------------------------------------------------

Sub ShadeEveryOtherRow()

Range("A2").EntireRow.Select
Do While ActiveCell.Value < ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End Sub
----------------------------------------------------------------

But if I try to have it run at open, it does nothing. I
have tried placing the code directly in workbook_open,
nothing happens. I have tried calling the sub in
workbook_open,

----------------------------------------------------------------

Private Sub Workbook_Open()
ShadeEveryOtherRow
End Sub
----------------------------------------------------------------

and that doesn't work either. What the heck is going on
here!?

Thanks,
gm

On Sat, 14 Aug 2004 22:28:56 -0600, JE McGimpsey
wrote:

RBUnless you don't have the Workbook_Open() code in the ThisWorkbook
RBmodule, it should work fine.
RB
RBHowever, instead of running a macro every time, why not select the
RBentire sheet and use the technique found he
RB
RB http://cpearson.com/excel/banding.htm

That's an extremely interesting method and works very well for
predefined cells. However i've just tried capturing the conditional
formatting key strokes in a macro and other than the first 'select',
nothing else appears.

I'd like to be able to apply this conditional formatting to new rows
introduced by some other VBA code. I could I suppose hold this
formatting in cells in a 'spare' row and copy the formatting each time
a row is introduced, but I'd like to know how to apply it directly
with code.

Any ideas?

Many thanks,

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

One way:

Public Sub ApplyCF()
With Range("A1:A" & Range("A" & Rows.Count).End( _
xlUp).Row).EntireRow.FormatConditions
.Delete
With .Add(Type:=xlExpression, _
Formula1:="=MOD(ROW(),2)=0")
.Interior.ColorIndex = 35
End With
End With
End Sub

Adjust your row definition as necessary.


In article ,
Richard Buttrey wrote:

That's an extremely interesting method and works very well for
predefined cells. However i've just tried capturing the conditional
formatting key strokes in a macro and other than the first 'select',
nothing else appears.

I'd like to be able to apply this conditional formatting to new rows
introduced by some other VBA code. I could I suppose hold this
formatting in cells in a 'spare' row and copy the formatting each time
a row is introduced, but I'd like to know how to apply it directly
with code.

Using Pearson's formula, the code is:

With Range("D1:D30")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW()-1,3*2)+1<=3"
.FormatConditions(1).Interior.ColorIndex = 35
End With

Mike F
"Richard Buttrey" wrote in
message ...
On Sat, 14 Aug 2004 22:28:56 -0600, JE McGimpsey
wrote:

RBUnless you don't have the Workbook_Open() code in the ThisWorkbook
RBmodule, it should work fine.
RB
RBHowever, instead of running a macro every time, why not select the
RBentire sheet and use the technique found he
RB
RB http://cpearson.com/excel/banding.htm

That's an extremely interesting method and works very well for
predefined cells. However i've just tried capturing the conditional
formatting key strokes in a macro and other than the first 'select',
nothing else appears.

I'd like to be able to apply this conditional formatting to new rows
introduced by some other VBA code. I could I suppose hold this
formatting in cells in a 'spare' row and copy the formatting each time
a row is introduced, but I'd like to know how to apply it directly
with code.

Any ideas?

Many thanks,

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

The Workbook_Open() code is in the ThisWorkbook module, and it still
doesn't work. Thanks for the suggestion, but I prefer running it on
open so that each time the worksheet opens it corrects for any changes
made in the last edits.
Guy

JE McGimpsey wrote:

Unless you don't have the Workbook_Open() code in the ThisWorkbook
module, it should work fine.

However, instead of running a macro every time, why not select the
entire sheet and use the technique found he

http://cpearson.com/excel/banding.htm

In article , wrote:

I have the following sub that will shade every other row in
a spreadsheet. It works fine if I run it manually via
alt-F8
----------------------------------------------------------------

Sub ShadeEveryOtherRow()

Range("A2").EntireRow.Select
Do While ActiveCell.Value < ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End Sub
----------------------------------------------------------------

But if I try to have it run at open, it does nothing. I
have tried placing the code directly in workbook_open,
nothing happens. I have tried calling the sub in
workbook_open,

----------------------------------------------------------------

Private Sub Workbook_Open()
ShadeEveryOtherRow
End Sub
----------------------------------------------------------------

and that doesn't work either. What the heck is going on
here!?

Thanks,
gm

How do you open the workbook with the workbook_open event? Do you open it
with a macro. Does the macro run if you open it manually?

--
Regards,
Tom Ogilvy

wrote in message ...
The Workbook_Open() code is in the ThisWorkbook module, and it still
doesn't work. Thanks for the suggestion, but I prefer running it on
open so that each time the worksheet opens it corrects for any changes
made in the last edits.
Guy

JE McGimpsey wrote:

Unless you don't have the Workbook_Open() code in the ThisWorkbook
module, it should work fine.

However, instead of running a macro every time, why not select the
entire sheet and use the technique found he

http://cpearson.com/excel/banding.htm

In article , wrote:

I have the following sub that will shade every other row in
a spreadsheet. It works fine if I run it manually via
alt-F8
----------------------------------------------------------------

Sub ShadeEveryOtherRow()

Range("A2").EntireRow.Select
Do While ActiveCell.Value < ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End Sub
----------------------------------------------------------------

But if I try to have it run at open, it does nothing. I
have tried placing the code directly in workbook_open,
nothing happens. I have tried calling the sub in
workbook_open,

----------------------------------------------------------------

Private Sub Workbook_Open()
ShadeEveryOtherRow
End Sub
----------------------------------------------------------------

and that doesn't work either. What the heck is going on
here!?

Thanks,
gm

I believe you are on to something Tom. I do open the file with a macro, and
if I open it manually, the worksheet_open event runs correctly. So now what?
gm

Tom Ogilvy wrote:

How do you open the workbook with the workbook_open event? Do you open it
with a macro. Does the macro run if you open it manually?

--
Regards,
Tom Ogilvy

wrote in message ...
The Workbook_Open() code is in the ThisWorkbook module, and it still
doesn't work. Thanks for the suggestion, but I prefer running it on
open so that each time the worksheet opens it corrects for any changes
made in the last edits.
Guy

JE McGimpsey wrote:

Unless you don't have the Workbook_Open() code in the ThisWorkbook
module, it should work fine.

However, instead of running a macro every time, why not select the
entire sheet and use the technique found he

http://cpearson.com/excel/banding.htm

In article , wrote:

I have the following sub that will shade every other row in
a spreadsheet. It works fine if I run it manually via
alt-F8
----------------------------------------------------------------

Sub ShadeEveryOtherRow()

Range("A2").EntireRow.Select
Do While ActiveCell.Value < ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End Sub
----------------------------------------------------------------

But if I try to have it run at open, it does nothing. I
have tried placing the code directly in workbook_open,
nothing happens. I have tried calling the sub in
workbook_open,

----------------------------------------------------------------

Private Sub Workbook_Open()
ShadeEveryOtherRow
End Sub
----------------------------------------------------------------

and that doesn't work either. What the heck is going on
here!?

Thanks,
gm

If you open it using a macro started with a shortcut key combination that
includes the Shift Key, then you need to use one that does not use the shift
key. the shift key seems to disable macros (similar to when you open a
workbook manually and hold down the shift key).

--
Regards,
Tom Ogilvy

wrote in message ...
I believe you are on to something Tom. I do open the file with a macro,
and
if I open it manually, the worksheet_open event runs correctly. So now
what?
gm

Tom Ogilvy wrote:

How do you open the workbook with the workbook_open event? Do you open
it
with a macro. Does the macro run if you open it manually?

--
Regards,
Tom Ogilvy

wrote in message ...
The Workbook_Open() code is in the ThisWorkbook module, and it still
doesn't work. Thanks for the suggestion, but I prefer running it on
open so that each time the worksheet opens it corrects for any changes
made in the last edits.
Guy

JE McGimpsey wrote:

Unless you don't have the Workbook_Open() code in the ThisWorkbook
module, it should work fine.

However, instead of running a macro every time, why not select the
entire sheet and use the technique found he

http://cpearson.com/excel/banding.htm

In article , wrote:

I have the following sub that will shade every other row in
a spreadsheet. It works fine if I run it manually via
alt-F8
----------------------------------------------------------------

Sub ShadeEveryOtherRow()

Range("A2").EntireRow.Select
Do While ActiveCell.Value < ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End Sub
----------------------------------------------------------------

But if I try to have it run at open, it does nothing. I
have tried placing the code directly in workbook_open,
nothing happens. I have tried calling the sub in
workbook_open,

----------------------------------------------------------------

Private Sub Workbook_Open()
ShadeEveryOtherRow
End Sub
----------------------------------------------------------------

and that doesn't work either. What the heck is going on
here!?

Thanks,
gm