Add "go to next" if error encountered

Hi, I have the following code that colors a cell different colors based on the date compared to today in Column F. (red if it is in the past, yellow if it is today, etc.)

Recently had an issue with blanks and the value "NULL" in that field that cause the macro to crash. Need to modify this so that if it detects an error condition, it just goes to the next row instead of crashing.

Thanks in advance,

swjtx99

Dim C As Variant
Dim CI As Integer
Dim Col As Variant
Dim FirstRow As Long
Dim LastRow As Long
Dim rng As Range
Dim Wks As Worksheet
Dim monOffset As Integer
Dim targetDate As Date

    Col = "F"
    FirstRow = 2   'Assumes header row is row 1
    Set Wks = Worksheets("TEST1")
    LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
    LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
   
    Set rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
        targetDate = Now()
    For Each C In rng
        monOffset = 0
       
        Select Case Int(targetDate) - C
        Case Is > 0
            CI = 3   'Red
        Case Is = 0
            CI = 6   'Yellow
        Case Is < 0
            Select Case Weekday(C)
            Case 2  'Monday
                Select Case Int(targetDate) - C
                Case -3
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case 3  'Tuesday
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case Else
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -2
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            End Select
        End Select
       
        C.Interior.ColorIndex = CI
    Next
swjtx99Asked:
Who is Participating?
 
Ken ButtersCommented:
I didn't realize this... but apparently I needed to "Resume" otherwise VBA thinks I'm still in error trapping mode from the first error.

This should work:

Dim C As Variant
Dim CI As Integer
Dim Col As Variant
Dim FirstRow As Long
Dim LastRow As Long
Dim rng As Range
Dim Wks As Worksheet
Dim monOffset As Integer
Dim targetDate As Date

    Col = "F"
    FirstRow = 2   'Assumes header row is row 1
    Set Wks = Worksheets("TEST1")
    LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
    LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
    
    Set rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
        targetDate = Now()
    For Each C In rng
        monOffset = 0
        
        On Error GoTo ContinueNext
        
        Select Case Int(targetDate) - C
        Case Is > 0
            CI = 3   'Red
        Case Is = 0
            CI = 6   'Yellow
        Case Is < 0
            Select Case Weekday(C)
            Case 2  'Monday
                Select Case Int(targetDate) - C
                Case -3
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case 3  'Tuesday
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case Else
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -2
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            End Select
        End Select
        
        C.Interior.ColorIndex = CI
ContinueNext:
        Err.Clear
        Resume ContinueNext2
ContinueNext2:
    Next

Open in new window

0
 
Ken ButtersCommented:
I've added on error statement...

and a "ContinueNext" label that should handle the error

Note : the "On Error GoTo 0" statement clears out the prior error.

Dim C As Variant
Dim CI As Integer
Dim Col As Variant
Dim FirstRow As Long
Dim LastRow As Long
Dim rng As Range
Dim Wks As Worksheet
Dim monOffset As Integer
Dim targetDate As Date

    Col = "F"
    FirstRow = 2   'Assumes header row is row 1
    Set Wks = Worksheets("TEST1")
    LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
    LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
    
    Set rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
        targetDate = Now()
    For Each C In rng
        monOffset = 0
        
        On Error GoTo ContinueNext
        
        Select Case Int(targetDate) - C
        Case Is > 0
            CI = 3   'Red
        Case Is = 0
            CI = 6   'Yellow
        Case Is < 0
            Select Case Weekday(C)
            Case 2  'Monday
                Select Case Int(targetDate) - C
                Case -3
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case 3  'Tuesday
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case Else
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -2
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            End Select
        End Select
        
        C.Interior.ColorIndex = CI
ContinueNext:
        On Error GoTo 0
    Next

Open in new window

0
 
swjtx99Author Commented:
Hi Ken,

Thanks for the reply,

I replaced 2 of the dates with the word NULL and it still errors out here:

   Select Case Int(targetDate) - C

Any ideas?

Thanks,
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Ken ButtersCommented:
The "On Error GoTo ContinueNext" should have caught that.


Can you post the modified code you used?

I want to confirm the placement of "On Error Goto ContinueNext".
0
 
swjtx99Author Commented:
Hi Ken,

Sure:

Dim C As Variant
Dim CI As Integer
Dim Col As Variant
Dim FirstRow As Long
Dim LastRow As Long
Dim rng As Range
Dim Wks As Worksheet
Dim monOffset As Integer
Dim targetDate As Date

    Col = "F"
    FirstRow = 2   'Assumes header row is row 1
    Set Wks = Worksheets("TEST1")
    LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
    LastRow = IIf(LastRow < FirstRow, FirstRow, LastRow)
   
    Set rng = Wks.Range(Cells(FirstRow, Col), Cells(LastRow, Col))
        targetDate = Now()
    For Each C In rng
        monOffset = 0
       
        On Error GoTo ContinueNext
       
        Select Case Int(targetDate) - C
        Case Is > 0
            CI = 3   'Red
        Case Is = 0
            CI = 6   'Yellow
        Case Is < 0
            Select Case Weekday(C)
            Case 2  'Monday
                Select Case Int(targetDate) - C
                Case -3
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case 3  'Tuesday
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -4
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            Case Else
                Select Case Int(targetDate) - C
                Case -1
                    CI = 4  'Green
                Case -2
                    CI = 35 'Pale Green
                Case Else
                    CI = xlColorIndexNone
                End Select
            End Select
        End Select
       
        C.Interior.ColorIndex = CI
ContinueNext:
        On Error GoTo 0
    Next
0
 
swjtx99Author Commented:
Thanks for your help.

Works great!

swjtx99
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.