Macro takes forever - didn't used to

Z

Zen

Hi,
I'm running a macro to rearrange some tables and add alternate row colors.
In the past this macro only took about 15 seconds to run and now it takes
several minutes. I can't recall installing any new apps besides windows
updates in a long time.

I didn't look at cpu usage before since it was so fast I didn't think
anything of it, but now it is like 40-50% in the process list.

kind of an old system but still workin hard.
Win XP with excel 2002
dual 1.8ghz xeon
1gb ram

I've copy/pasted the macro.

Sub Inventory()
'
' Inventory Macro
' Stip "The" from titles and rearrange
'

'
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("B1").Select
Application.WindowState = xlNormal
Range("B1").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = ""
Range("B1").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[3],4)=""The
"",MID(RC[3],5,255),RC[3])"
Range("B1").Select
Selection.Copy
Range("B2").Select
ActiveWindow.SmallScroll Down:=75
Range("B2:B79").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-42
Rows("1:1").Select
Selection.Font.Bold = True
ActiveWindow.WindowState = xlMaximized
Columns("B:B").Select
Selection.ColumnWidth = 28.86
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
With ActiveWindow
.Top = 1.75
.Left = 1.75
End With
With ActiveWindow
.Width = 843.75
.Height = 320.25
End With
Columns("A:A").ColumnWidth = 10.57
Selection.ColumnWidth = 75.86
Range("A3").Select
Columns("A:A").ColumnWidth = 11.43
Columns("C:C").ColumnWidth = 14.86
Columns("D:D").Select
Selection.ClearContents
Range("D12").Select

Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24

End Sub
 
B

Bernie Deitrick

Cells (Cells.Select) can be a dangerous way to select a range, if you only mean to use a small
part of a sheet. Try pressing Ctrl-End and see where you end up - you may need to delete extra rows
and columns that have been included in your usedrange, or just use the block of cells that are
filled - instead of Cells.Select use, perhaps, Range("B2").CurrentRegion.Select


HTH,
Bernie
MS Excel MVP


Zen said:
Hi,
I'm running a macro to rearrange some tables and add alternate row colors. In the past this macro
only took about 15 seconds to run and now it takes several minutes. I can't recall installing any
new apps besides windows updates in a long time.

I didn't look at cpu usage before since it was so fast I didn't think anything of it, but now it
is like 40-50% in the process list.

kind of an old system but still workin hard.
Win XP with excel 2002
dual 1.8ghz xeon
1gb ram

I've copy/pasted the macro.

Sub Inventory()
'
' Inventory Macro
' Stip "The" from titles and rearrange
'

'
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("B1").Select
Application.WindowState = xlNormal
Range("B1").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = ""
Range("B1").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[3],4)=""The "",MID(RC[3],5,255),RC[3])"
Range("B1").Select
Selection.Copy
Range("B2").Select
ActiveWindow.SmallScroll Down:=75
Range("B2:B79").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-42
Rows("1:1").Select
Selection.Font.Bold = True
ActiveWindow.WindowState = xlMaximized
Columns("B:B").Select
Selection.ColumnWidth = 28.86
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
With ActiveWindow
.Top = 1.75
.Left = 1.75
End With
With ActiveWindow
.Width = 843.75
.Height = 320.25
End With
Columns("A:A").ColumnWidth = 10.57
Selection.ColumnWidth = 75.86
Range("A3").Select
Columns("A:A").ColumnWidth = 11.43
Columns("C:C").ColumnWidth = 14.86
Columns("D:D").Select
Selection.ClearContents
Range("D12").Select

Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24

End Sub
Click to expand...
 
D

Don Guillett

The whole thing should be re-written to NOT select and to not use CELLS.
However, the thing that is probably slowing it the most is the page setup.
Better to do that separately and only to make changes that are necessary. In
fact, it looks like it is doing nothing so comment it out to test. Remove
selections, get rid of the scrolldowns, etc. Post back for more help.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Zen said:
Hi,
I'm running a macro to rearrange some tables and add alternate row colors.
In the past this macro only took about 15 seconds to run and now it takes
several minutes. I can't recall installing any new apps besides windows
updates in a long time.

I didn't look at cpu usage before since it was so fast I didn't think
anything of it, but now it is like 40-50% in the process list.

kind of an old system but still workin hard.
Win XP with excel 2002
dual 1.8ghz xeon
1gb ram

I've copy/pasted the macro.

Sub Inventory()
'
' Inventory Macro
' Stip "The" from titles and rearrange
'

'
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("B1").Select
Application.WindowState = xlNormal
Range("B1").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = ""
Range("B1").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[3],4)=""The
"",MID(RC[3],5,255),RC[3])"
Range("B1").Select
Selection.Copy
Range("B2").Select
ActiveWindow.SmallScroll Down:=75
Range("B2:B79").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-42
Rows("1:1").Select
Selection.Font.Bold = True
ActiveWindow.WindowState = xlMaximized
Columns("B:B").Select
Selection.ColumnWidth = 28.86
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
With ActiveWindow
.Top = 1.75
.Left = 1.75
End With
With ActiveWindow
.Width = 843.75
.Height = 320.25
End With
Columns("A:A").ColumnWidth = 10.57
Selection.ColumnWidth = 75.86
Range("A3").Select
Columns("A:A").ColumnWidth = 11.43
Columns("C:C").ColumnWidth = 14.86
Columns("D:D").Select
Selection.ClearContents
Range("D12").Select

Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24

End Sub
Click to expand...
 
Z

Zen

Ctrl+End brings me to the last row which is only 69. But it can change from
week to week up or down a although only by a few rows, that's why i select
them all.

I tried Rage like you suggested but it still took about the same time.

Also, that ended up messing up my data. because it didn't expand selection
so the quantities did not match up properly with the items they belonged to.

Bernie Deitrick said:
Cells (Cells.Select) can be a dangerous way to select a range, if you
only mean to use a small part of a sheet. Try pressing Ctrl-End and see
where you end up - you may need to delete extra rows and columns that have
been included in your usedrange, or just use the block of cells that are
filled - instead of Cells.Select use, perhaps,
Range("B2").CurrentRegion.Select


HTH,
Bernie
MS Excel MVP


Zen said:
Hi,
I'm running a macro to rearrange some tables and add alternate row
colors. In the past this macro only took about 15 seconds to run and now
it takes several minutes. I can't recall installing any new apps besides
windows updates in a long time.

I didn't look at cpu usage before since it was so fast I didn't think
anything of it, but now it is like 40-50% in the process list.

kind of an old system but still workin hard.
Win XP with excel 2002
dual 1.8ghz xeon
1gb ram

I've copy/pasted the macro.

Sub Inventory()
'
' Inventory Macro
' Stip "The" from titles and rearrange
'

'
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("B1").Select
Application.WindowState = xlNormal
Range("B1").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = ""
Range("B1").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[3],4)=""The
"",MID(RC[3],5,255),RC[3])"
Range("B1").Select
Selection.Copy
Range("B2").Select
ActiveWindow.SmallScroll Down:=75
Range("B2:B79").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-42
Rows("1:1").Select
Selection.Font.Bold = True
ActiveWindow.WindowState = xlMaximized
Columns("B:B").Select
Selection.ColumnWidth = 28.86
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
With ActiveWindow
.Top = 1.75
.Left = 1.75
End With
With ActiveWindow
.Width = 843.75
.Height = 320.25
End With
Columns("A:A").ColumnWidth = 10.57
Selection.ColumnWidth = 75.86
Range("A3").Select
Columns("A:A").ColumnWidth = 11.43
Columns("C:C").ColumnWidth = 14.86
Columns("D:D").Select
Selection.ClearContents
Range("D12").Select

Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24

End Sub
Click to expand...
Click to expand...
 
Z

Zen

Well, I'm not familiar with scripting really. I just used the record
function to make the macro.

I'll just live with the delay.


Don Guillett said:
The whole thing should be re-written to NOT select and to not use CELLS.
However, the thing that is probably slowing it the most is the page setup.
Better to do that separately and only to make changes that are necessary.
In fact, it looks like it is doing nothing so comment it out to test.
Remove selections, get rid of the scrolldowns, etc. Post back for more
help.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Zen said:
Hi,
I'm running a macro to rearrange some tables and add alternate row
colors. In the past this macro only took about 15 seconds to run and now
it takes several minutes. I can't recall installing any new apps besides
windows updates in a long time.

I didn't look at cpu usage before since it was so fast I didn't think
anything of it, but now it is like 40-50% in the process list.

kind of an old system but still workin hard.
Win XP with excel 2002
dual 1.8ghz xeon
1gb ram

I've copy/pasted the macro.

Sub Inventory()
'
' Inventory Macro
' Stip "The" from titles and rearrange
'

'
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("B1").Select
Application.WindowState = xlNormal
Range("B1").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = ""
Range("B1").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[3],4)=""The
"",MID(RC[3],5,255),RC[3])"
Range("B1").Select
Selection.Copy
Range("B2").Select
ActiveWindow.SmallScroll Down:=75
Range("B2:B79").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-42
Rows("1:1").Select
Selection.Font.Bold = True
ActiveWindow.WindowState = xlMaximized
Columns("B:B").Select
Selection.ColumnWidth = 28.86
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
With ActiveWindow
.Top = 1.75
.Left = 1.75
End With
With ActiveWindow
.Width = 843.75
.Height = 320.25
End With
Columns("A:A").ColumnWidth = 10.57
Selection.ColumnWidth = 75.86
Range("A3").Select
Columns("A:A").ColumnWidth = 11.43
Columns("C:C").ColumnWidth = 14.86
Columns("D:D").Select
Selection.ClearContents
Range("D12").Select

Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24

End Sub
Click to expand...
Click to expand...
 
D

Don Guillett

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Zen said:
Well, I'm not familiar with scripting really. I just used the record
function to make the macro.

I'll just live with the delay.


Don Guillett said:
The whole thing should be re-written to NOT select and to not use CELLS.
However, the thing that is probably slowing it the most is the page
setup. Better to do that separately and only to make changes that are
necessary. In fact, it looks like it is doing nothing so comment it out
to test. Remove selections, get rid of the scrolldowns, etc. Post back
for more help.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Zen said:
Hi,
I'm running a macro to rearrange some tables and add alternate row
colors. In the past this macro only took about 15 seconds to run and now
it takes several minutes. I can't recall installing any new apps besides
windows updates in a long time.

I didn't look at cpu usage before since it was so fast I didn't think
anything of it, but now it is like 40-50% in the process list.

kind of an old system but still workin hard.
Win XP with excel 2002
dual 1.8ghz xeon
1gb ram

I've copy/pasted the macro.

Sub Inventory()
'
' Inventory Macro
' Stip "The" from titles and rearrange
'

'
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Cut
Columns("E:E").Select
ActiveSheet.Paste
Range("B1").Select
Application.WindowState = xlNormal
Range("B1").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = ""
Range("B1").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[3],4)=""The
"",MID(RC[3],5,255),RC[3])"
Range("B1").Select
Selection.Copy
Range("B2").Select
ActiveWindow.SmallScroll Down:=75
Range("B2:B79").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-42
Rows("1:1").Select
Selection.Font.Bold = True
ActiveWindow.WindowState = xlMaximized
Columns("B:B").Select
Selection.ColumnWidth = 28.86
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
ActiveWindow.WindowState = xlNormal
With ActiveWindow
.Top = 1.75
.Left = 1.75
End With
With ActiveWindow
.Width = 843.75
.Height = 320.25
End With
Columns("A:A").ColumnWidth = 10.57
Selection.ColumnWidth = 75.86
Range("A3").Select
Columns("A:A").ColumnWidth = 11.43
Columns("C:C").ColumnWidth = 14.86
Columns("D:D").Select
Selection.ClearContents
Range("D12").Select

Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating Pivot Table with Macro using Multiple Sheets 0
Macro to Select text from a point given to a specific word 2
Macro to format multiple worksheets 24
VBA for Dependents shortcut menu 0
Modify range in VBA 9
Excel Macro to add "Page n of n" 3
Modification of a recorded Macro so new sheets take on cell name? 0
Macro - very slow run in 2003 3

Top