|
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 |
VBA Conditional Formatting to UsedRange on Worksheet
See response in public.excel.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RyanH" wrote in message ... 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 |
VBA Conditional Formatting to UsedRange on Worksheet
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. "Bob Phillips" wrote: See response in public.excel. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RyanH" wrote in message ... 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 |
| All times are GMT +1. The time now is 10:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com