|
help with Run-time error '1004'
I am fairly new at VBA programming and am trying to conditional format
rows in a sheet based on the values in two columns. I keep getting this error: "Run-time error '1004': Application-defined or object-defined error Can anyone please explain why? Here is the code. ------------------------- Private Sub RowColor() Dim Row As Integer Dim Status As String Dim Workdays As Integer Dim n As Integer 'For each row, check the status and the workdays remaining to due date. Color the row accordingly. Status = ActiveSheet.Cells(Row, 10) Workdays = ActiveSheet.Cells(Row, 14) n = ActiveSheet.UsedRange.Rows.Count For Row = 2 To n Select Case Status Case NullString Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False Case "Pending" Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False Case "Resolved" Target.EntireRow.Interior.ColorIndex = 4 'green Target.EntireRow.Font.Bold = False Case "Hold" Target.EntireRow.Interior.ColorIndex = 5 'Blue Target.EntireRow.Font.Bold = False Case "Active" If Workdays <= 1 Then Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = True ElseIf Workdays 1 Then Target.EntireRow.Interior.ColorIndex = 6 'Orange Target.EntireRow.Font.Bold = True ElseIf Workdays = 10 Then Target.EntireRow.Interior.ColorIndex = 7 'Red Target.EntireRow.Font.Bold = True End If Case Else Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False End Select Next End Sub |
help with Run-time error '1004'
What is Target? What is NullString?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "puttocks" wrote in message ... I am fairly new at VBA programming and am trying to conditional format rows in a sheet based on the values in two columns. I keep getting this error: "Run-time error '1004': Application-defined or object-defined error Can anyone please explain why? Here is the code. ------------------------- Private Sub RowColor() Dim Row As Integer Dim Status As String Dim Workdays As Integer Dim n As Integer 'For each row, check the status and the workdays remaining to due date. Color the row accordingly. Status = ActiveSheet.Cells(Row, 10) Workdays = ActiveSheet.Cells(Row, 14) n = ActiveSheet.UsedRange.Rows.Count For Row = 2 To n Select Case Status Case NullString Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False Case "Pending" Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False Case "Resolved" Target.EntireRow.Interior.ColorIndex = 4 'green Target.EntireRow.Font.Bold = False Case "Hold" Target.EntireRow.Interior.ColorIndex = 5 'Blue Target.EntireRow.Font.Bold = False Case "Active" If Workdays <= 1 Then Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = True ElseIf Workdays 1 Then Target.EntireRow.Interior.ColorIndex = 6 'Orange Target.EntireRow.Font.Bold = True ElseIf Workdays = 10 Then Target.EntireRow.Interior.ColorIndex = 7 'Red Target.EntireRow.Font.Bold = True End If Case Else Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False End Select Next End Sub |
help with Run-time error '1004'
On Jan 7, 4:18*pm, puttocks wrote:
I am fairly new at VBA programming and am trying to conditional format rows in a sheet based on the values in two columns. *I keep getting this error: "Run-time error '1004': Application-defined or object-defined error Can anyone please explain why? Here is the code. ------------------------- Private Sub RowColor() * * Dim Row As Integer * * Dim Status As String * * Dim Workdays As Integer * * Dim n As Integer * * 'For each row, check the status and the workdays remaining to due date. *Color the row accordingly. * * Status = ActiveSheet.Cells(Row, 10) * * Workdays = ActiveSheet.Cells(Row, 14) * * n = ActiveSheet.UsedRange.Rows.Count * * For Row = 2 To n * * * * Select Case Status * * * * Case NullString * * * * * * Target.EntireRow.Interior.ColorIndex = xlNone * * * * * * Target.EntireRow.Font.Bold = False * * * * Case "Pending" * * * * * * Target.EntireRow.Interior.ColorIndex = xlNone * * * * * * Target.EntireRow.Font.Bold = False * * * * Case "Resolved" * * * * * * Target.EntireRow.Interior.ColorIndex = 4 * * 'green * * * * * * Target.EntireRow.Font.Bold = False * * * * Case "Hold" * * * * * * Target.EntireRow.Interior.ColorIndex = 5 * *'Blue * * * * * * Target.EntireRow.Font.Bold = False * * * * Case "Active" * * * * * * If Workdays <= 1 Then * * * * * * * * Target.EntireRow.Interior.ColorIndex = xlNone * * * * * * * * Target.EntireRow.Font.Bold = True * * * * * * ElseIf Workdays 1 Then * * * * * * * * Target.EntireRow.Interior.ColorIndex = 6 'Orange * * * * * * * * Target.EntireRow.Font.Bold = True * * * * * * ElseIf Workdays = 10 Then * * * * * * * * Target.EntireRow.Interior.ColorIndex = 7 'Red * * * * * * * * Target.EntireRow.Font.Bold = True * * * * * * End If * * * * Case Else * * * * * * Target.EntireRow.Interior.ColorIndex = xlNone * * * * * * Target.EntireRow.Font.Bold = False * * * * End Select * * *Next End Sub Hi You havn't given the variable Row a value. Status = ActiveSheet.Cells(Row, 10) Workdays = ActiveSheet.Cells(Row, 14) The code then assumes it has value 0 as it is declared an integer. But Cells(0, 10) does not exist, as rows start at 1. So do Row = 1 at the top of the code. Better still, change Row to myRow as Row is a VBA Range property. regards Paul |
help with Run-time error '1004'
Try this:
Private Sub RowColor() Dim Row As Integer Dim Status As String Dim Workdays As Integer Dim n As Integer 'For each row, check the status and the workdays remaining to due 'date. Color the row accordingly. n = ActiveSheet.UsedRange.Rows.Count For Row = 2 To n Status = ActiveSheet.Cells(Row, 10) Workdays = ActiveSheet.Cells(Row, 14) Select Case Status Case "" Cells(Row, 1).EntireRow.Interior.ColorIndex = xlNone Cells(Row, 1).EntireRow.Font.Bold = False Case "Pending" Cells(Row, 1).EntireRow.Interior.ColorIndex = xlNone Cells(Row, 1).EntireRow.Font.Bold = False Case "Resolved" Cells(Row, 1).EntireRow.Interior.ColorIndex = 4 'green Cells(Row, 1).EntireRow.Font.Bold = False Case "Hold" Cells(Row, 1).EntireRow.Interior.ColorIndex = 5 'Blue Cells(Row, 1).EntireRow.Font.Bold = False Case "Active" If Workdays <= 1 Then Cells(Row, 1).EntireRow.Interior.ColorIndex = xlNone Cells(Row, 1).EntireRow.Font.Bold = True ElseIf Workdays 1 Then Cells(Row, 1).EntireRow.Interior.ColorIndex = 6 'Orange Cells(Row, 1).EntireRow.Font.Bold = True ElseIf Workdays = 10 Then Cells(Row, 1).EntireRow.Interior.ColorIndex = 7 'Red Cells(Row, 1).EntireRow.Font.Bold = True End If Case Else Cells(Row, 1).EntireRow.Interior.ColorIndex = xlNone Cells(Row, 1).EntireRow.Font.Bold = False End Select Next End Sub "puttocks" wrote: I am fairly new at VBA programming and am trying to conditional format rows in a sheet based on the values in two columns. I keep getting this error: "Run-time error '1004': Application-defined or object-defined error Can anyone please explain why? Here is the code. ------------------------- Private Sub RowColor() Dim Row As Integer Dim Status As String Dim Workdays As Integer Dim n As Integer 'For each row, check the status and the workdays remaining to due date. Color the row accordingly. Status = ActiveSheet.Cells(Row, 10) Workdays = ActiveSheet.Cells(Row, 14) n = ActiveSheet.UsedRange.Rows.Count For Row = 2 To n Select Case Status Case NullString Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False Case "Pending" Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False Case "Resolved" Target.EntireRow.Interior.ColorIndex = 4 'green Target.EntireRow.Font.Bold = False Case "Hold" Target.EntireRow.Interior.ColorIndex = 5 'Blue Target.EntireRow.Font.Bold = False Case "Active" If Workdays <= 1 Then Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = True ElseIf Workdays 1 Then Target.EntireRow.Interior.ColorIndex = 6 'Orange Target.EntireRow.Font.Bold = True ElseIf Workdays = 10 Then Target.EntireRow.Interior.ColorIndex = 7 'Red Target.EntireRow.Font.Bold = True End If Case Else Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False End Select Next End Sub |
help with Run-time error '1004'
Thx for your replies. I declared "target" and removed the
"nullstring". I also set n to 0. Now I don't get any errors, but strangely, no formatting of any kind occurs. Clearly I am missing something. Thanks in advance for your time. v ---------------------------------------------- Private Sub RowColor(ByVal Target As Excel.Range) Dim myRow As Integer Dim Status As String Dim Workdays As Integer Dim n As Integer 'For each row, check the status and the workdays remaining to due date. Color the row accordingly. Status = ActiveSheet.Cells(Row, 10) Workdays = ActiveSheet.Cells(Row, 14) n = ActiveSheet.UsedRange.Rows.Count myRow = 0 For myRow = 2 To n Select Case Status Case "Pending" Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False Case "Resolved" Target.EntireRow.Interior.ColorIndex = 4 'green Target.EntireRow.Font.Bold = False Case "Hold" Target.EntireRow.Interior.ColorIndex = 5 'Blue Target.EntireRow.Font.Bold = False Case "Active" If Workdays <= 1 Then Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = True ElseIf Workdays 1 Then Target.EntireRow.Interior.ColorIndex = 6 'Orange Target.EntireRow.Font.Bold = True ElseIf Workdays = 10 Then Target.EntireRow.Interior.ColorIndex = 7 'Red Target.EntireRow.Font.Bold = True End If Case Else Target.EntireRow.Interior.ColorIndex = xlNone Target.EntireRow.Font.Bold = False End Select Next End Sub |
help with Run-time error '1004'
Thx Smallweed. Your code executed perfectly.
v |
| All times are GMT +1. The time now is 09:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com