LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 586
Default VBA Conditional Formatting to UsedRange on Worksheet

I am getting an error indicated below in my code (Application-defined or
object-defined Error, Run-Time Error '1004'). I want to have a border every
5 lines. Can someone explain to me why this portion of code will not work?
Huge THANKS in advanced!!!

Sub JobTracker1()

Application.ScreenUpdating = False

Call shUnProtect

RowCount = 3

With Sheets("Archive")
Do While .Range("L" & RowCount) < ""
myMonth = Format(.Range("L" & RowCount), "mmmm")
Application.StatusBar = "Moving Sales Orders from Archive to " &
myMonth & "...Please Wait."
With Sheets(myMonth)
If IsEmpty(.Range("A3")) = True Then
Sheets("Archive").Range("A" & RowCount & ":P" & RowCount).Cut
.Paste Destination:=.Range("A3")
Sheets("Archive").Range("Q" & RowCount & ":BO" & RowCount).Cut
.Paste Destination:=.Range("T3")
Else
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
Sheets("Archive").Range("A" & RowCount & ":P" & RowCount).Cut
.Paste Destination:=.Range("A" & NewRow)
Sheets("Archive").Range("Q" & RowCount & ":BO" & RowCount).Cut
.Paste Destination:=.Range("T" & NewRow)
End If
With .Cells
.Interior.ColorIndex = 41
.Font.ColorIndex = 2
End With
.UsedRange.FormatConditions.Add Type:=xlExpression,
Formula1:="=MOD(ROW(),5)=0" <==ERROR ERROR ERROR
With .UsedRange.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
RowCount = RowCount + 1
Loop
End With

Application.StatusBar = False
Application.CutCopyMode = False
Application.ScreenUpdating = False

Call shProtect

End Sub

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Redefine UsedRange property of Worksheet Object ExcelMonkey Excel Programming 3 December 20th 07 09:45 PM
Saving worksheet as CSV with correct usedrange... Mike Iacovou Excel Programming 1 November 14th 07 03:24 AM
Why is worksheet.usedrange empty? Sara Excel Worksheet Functions 2 August 24th 07 03:38 PM
Redefining the UsedRange of a Worksheet ExcelMonkey[_189_] Excel Programming 1 February 13th 05 06:34 PM
UsedRange & Formatting of Cells JStone0218 Excel Programming 3 October 26th 03 11:36 PM


All times are GMT +1. The time now is 05:19 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"