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]

You can't mix conditions like that in a Select Case Statement. If you area selecting cases based on some value, then the Case statements all refer to that value.

You can do Select Case True, and follow each Case keyword with any logical expression you like, but that's not a good fit for any but the last condition you tried to add.

You could do this:

If thiscondition then
   do this
else
   select case someValue
     case "bob" (meaning someValue = "bob")
        ...
      case "joe"
   end select
endif

 

[asad]

Thanks for coming to my aid again shg.

My problem is that I have to compare each value in range I4:BC200 against its corressponding value starting from say range EI4 to what ever it comes. The value in I4 and EI4 will be exactly same and so on with every value. How can I add this in my VBA to check each value and if it is different to the value in corressponding cell, then it should change the font to red.

If it has to be different code, then so be it but I will have the orginal code running at the same time. So if there is another code, it will have to accommodate the first code. Or may be we can have a code that will do all of that in one go. Any suggestions?

Asad

 

[shg]

I don't know the logic your intended logic, asad. It could be this:

    For Each c In Rng
        If c.Value <> c.Offset(, -8).Value Then
            c.Font.ColorIndex = 3
        End If
 
        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
    Next c

... or this

    For Each c In Rng
        If c.Value <> c.Offset(, -8).Value Then
            c.Font.ColorIndex = 3
        
        Else
            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
        End If
    Next c

... or something else.

 

[asad]

Thanks a lot shg, I tried both codes. I couldn't second one to work (I must be doing something wrong) but the first one is working perfect.
Thanks for your help.

Asad

 

[shg]

Perfect is good. You're welcome.

 

[asad]

Just for my curosity, in the first code, is it possible to get that if statement after the case statement. The reason being is that if I change a value from number to number, it works, but if I change it from say 5 to RDO, it doesn't come in red. It formats according to the case scenario as cell being light yellow and font being bold and blue.
It should be in red because it is not the same value as in corressponding cell. Same problem is with OFF.
If it is too much trouble, then don't worry about it. I am happy with the code you gave to me.

Asad

 

[shg]

Yes, you can certainly move the If statement.

 

[asad]

I tried it already but it gave me the same error message as in the first instance. I must be the most stupid person you ever came across. But, I need some more help to work it out. The code I got now 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
         
        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
        For Each c In rng
        If c.Value <> c.Offset(, -8).Value Then
            c.Font.ColorIndex = 3
            Else
            If c.Value = c.Offset(, -8).Value Then
            c.Font.ColorIndex = 1
            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

Asad

 

[asad]

Don't worry shg, I got that part right.
But can't get it to change the format of cell to normal with alternate rows being grey and white if I change a cell originally with RDO to any number say between 1 and 900.

Asad