swjtx99
asked on
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
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
ASKER
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,
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,
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".
Can you post the modified code you used?
I want to confirm the placement of "On Error Goto ContinueNext".
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help.
Works great!
swjtx99
Works great!
swjtx99
and a "ContinueNext" label that should handle the error
Note : the "On Error GoTo 0" statement clears out the prior error.
Open in new window