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
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