help with Run-time error '1004'

P

puttocks

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
 
B

Bob Phillips

What is Target? What is NullString?

--
HTH

Bob

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

paul.robinson

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
Click to expand...

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
 
S

Smallweed

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
 
P

puttocks

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
 

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

Similar Threads

conditional formatting with vba 3
Run-time error 1004 8
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count > 1"don't work 4
add a chart in a Add-In 1
Run-time error 1004 paste method of worksheet class failed 3
Run Time Error 1004 2
Run-time error '1004': PasteSpecial method of Range class failed 1
Worksheet event to change formatting when date expires. 5

Top