| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Redefine UsedRange property of Worksheet Object | Excel Programming | |||
| Saving worksheet as CSV with correct usedrange... | Excel Programming | |||
| Why is worksheet.usedrange empty? | Excel Worksheet Functions | |||
| Redefining the UsedRange of a Worksheet | Excel Programming | |||
| UsedRange & Formatting of Cells | Excel Programming | |||
Hybrid Mode
