Unable to get the error fixed in VBA

[asad]

Hello Guys,

I was able to put two codes together and it worked for me, but when I tried to insert another line, it gave me an error. I am unable to fix that error. Can you please help me? The error message is :

Error in Worksheet Change Event Code: Application-defined or Object-defined error

The actual code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
Dim Counter As Long
For Counter = 1 To Selection.Rows.Count
If Counter Mod 2 = 1 Then
Selection.Rows(Counter).Interior.ColorIndex = 15
End If
Next Counter
On Error GoTo Handler
Application.EnableEvents = False
    
    Set rng = Intersect(Target, Range("I4:BC200"))
    
    If rng Is Nothing Then
        GoTo My_Exit
    Else
        For Each c In rng
            Select Case c.Value
                Case "RDO"
                    c.Interior.ColorIndex = 19
                    c.Font.ColorIndex = 41
                    c.Font.Bold = True
                Case "OFF"
                    c.Interior.ColorIndex = 35
                Case "1", "2", "4", "128", "139", "142", "143", "145", "749"
                    c.Interior.ColorIndex = 45
                [COLOR=red][B]Case c.Value <> c.Offset(, -8).Value
                    c.Font.ColorIndex = 3
[/B][/COLOR]                
            End Select
        Next c
        
    End If
    
My_Exit:
Application.EnableEvents = True
Exit Sub
Handler:
MsgBox "Error in Worksheet Change Event Code: " & Err.Description
Resume My_Exit
End Sub

The line in red is the one that triggered that error message. How can I fix that?

Any help is appreciated.

Asad

 

[shg]

    For Each c In Rng
        Select Case c.Value
            Case "RDO"
                c.Interior.ColorIndex = 19
                c.Font.ColorIndex = 41
                c.Font.Bold = True
            Case "OFF"
                c.Interior.ColorIndex = 35
            Case "1", "2", "4", "128", "139", "142", "143", "145", "749"
                c.Interior.ColorIndex = 45
        End Select
        
        If c.Value <> c.Offset(, -8).Value Then
            c.Font.ColorIndex = 3
        End If
    Next c

 

[asad]

It works, but then there is another problem with this code. I am not able to find what is wrong. First I will post a screen shot.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1
2
3
4									Monday
5									39224
6									RDO
7
8	OFF								OFF
9	206								206
10	201								204
11	202								202
12	203								203
13	204								204
14	205								205
15	14								14
16	RDO								RDO
17	37								37
18	OFF								OFF
19									33
20									23
21									18
22									51
Sheet2

You see 204 in I10, it is in red and that's the way it should be. But it makes the cell I11 go grey. It should stay white as was before. I didn't even change the value in I11. Why did it change then? Can't understand

Asad

 

[asad]

May be something to do with the counter part of code!!!!!!!!!!!!!!!!!!

 

[asad]

Finally got it working the way I wanted it to.
It is like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
Dim Counter As Long
For Counter = 1 To Selection.Rows.Count
        If Counter Mod 2 = 1 Then
            Selection.Rows(Counter).Interior.ColorIndex = 15
        End If
    Next Counter
On Error GoTo Handler
Application.EnableEvents = False
 
    Set rng = Intersect(Target, Range("I4:BC200"))
 
    If rng Is Nothing Then
        GoTo My_Exit
    Else
         For Each c In rng
        Select Case c.Value
            Case "RDO"
                c.Interior.ColorIndex = 19
                c.Font.ColorIndex = 41
                c.Font.Bold = True
            Case "OFF"
                c.Interior.ColorIndex = 35
            Case "1", "2", "4", "128", "139", "142", "143", "145", "749"
                c.Interior.ColorIndex = 45
        End Select
 
        If c.Value <> c.Offset(, -8).Value Then
            c.Font.ColorIndex = 3
            Else
                If c.Value = "RDO" Then
                c.Interior.ColorIndex = 19
                c.Font.ColorIndex = 41
                c.Font.Bold = True
                Else
                    If c.Value = "OFF" Then
                    c.Interior.ColorIndex = 35
                    c.Font.ColorIndex = 1
                    Else
                        If c.Value = c.Offset(, -8).Value Then
                        c.Font.ColorIndex = 1
                        c.Font.Bold = False
                        End If
                    End If
                End If
        End If
 
    Next c
 
    End If
 
 
My_Exit:
Application.EnableEvents = True
Exit Sub
Handler:
MsgBox "Error in Worksheet Change Event Code: " & Err.Description
Resume My_Exit
End Sub

Thanks a lot shg. Without your help, I wouldn't have been able to do this.

Asad