VBA Conditional Formatting to UsedRange on Worksheet

R

RyanH

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
 
B

Bob Phillips

See response in public.excel.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

RyanH

I am new to VBA and I definitely could be wrong, but I believe that I am only
adding 1 condition in this code. I did verify that there are no current
conditonal formats on the worksheets in the workbook.

I'm just not sure why I am getting an error.
 

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

Top