On error run next case statement

[excelis]

Dear All,

in a previous thread which you can see here,

http://www.mrexcel.com/forum/showthread.php?p=2617843

i was advised to use the Case statement which worked perfectly. In some cases however, where my active cell is for example in position C3 the case ActiveCel.Offset(1,-4) gives of course an error!

Do you know how and where i should write that if there is an error, ignore it and continue with the next case statement??

Thank you in advance!!

 

[excelis]

Maybe it would be easier to post once again the code also here

 Select Case True
            
                    Case ActiveCell.Offset(1, 0) = 0
                      ActiveCell.Offset(1, 0).Range("A1").Select
                      With Selection.Interior
                       .Pattern = xlSolid
                       .PatternColorIndex = xlAutomatic
                       .Color = 65535
                       .TintAndShade = 0
                       .PatternTintAndShade = 0
                      End With
            
                    Case ActiveCell.Offset(1, 1) = 0
                      ActiveCell.Offset(1, 1).Range("A1").Select
                      With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                      End With
            
                    Case ActiveCell.Offset(1, -1) = 0
                      ActiveCell.Offset(1, -1).Range("A1").Select
                      With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                      End With
            
                    Case ActiveCell.Offset(1, 2) = 0
                      ActiveCell.Offset(1, 1).Range("A1").Select
                      With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                      End With
            
                    Case ActiveCell.Offset(1, -2) = 0
                      ActiveCell.Offset(1, -1).Range("A1").Select
                      With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                      End With
            
                    Case ActiveCell.Offset(1, 3) = 0
                      ActiveCell.Offset(1, 1).Range("A1").Select
                      With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                      End With
            
                    Case ActiveCell.Offset(1, -3) = 0
                      ActiveCell.Offset(1, -1).Range("A1").Select
                      With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                      End With
            
                    Case ActiveCell.Offset(1, 4) = 0
                      ActiveCell.Offset(1, 1).Range("A1").Select
                      With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                      End With
            
                    Case ActiveCell.Offset(1, -4) = 0
                      ActiveCell.Offset(1, -1).Range("A1").Select
                       With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                      End With
End Select

 

[shg]

Perhaps ...

Sub x()
    Dim j           As Long
    Dim k           As Long
 
    With ActiveCell.Offset(1)
        If .Value = 0 Then
            j = 0

        Else
            For j = 1 To 4
                If j + .Column <= Columns.Count Then
                    If .Offset(, j) = 0 Then
                        k = j
                        Exit For

                    ElseIf .Column - j >= 1 Then
                        If .Offset(, -j) = 0 Then
                            k = -j
                            Exit For
                        End If
                    End If
                End If
            Next j
            If j > 4 Then Exit Sub
        End If
 
        With .Offset(, k).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End With
End Sub

 

[excelis]

Thank you shg for the reply! I have tried the code but it gives me an error on

With . Offset(, k).Interior

saying error 438 object doesn't support this property or method

Any ideas why is this happening?

 

[shg]

You have a space between the dot and the Offset.

 

[excelis]

Thank you! you were correct! I put a space by accident! I also modified the k=j to k=1 or -1 as i wanted to move one space and i added the active cell move to the position it paints. It works now like a charm!!

    Dim j As Long
    Dim k As Long
 
    With ActiveCell.Offset(1)
        If .Value = 0 Then
            j = 0

        Else
            For j = 1 To 4
                If j + .Column <= Columns.Count Then
                    If .Offset(, j) = 0 Then
                        k = 1
                        Exit For

                    ElseIf .Column - j >= 1 Then
                        If .Offset(, -j) = 0 Then
                            k = -1
                            Exit For
                        End If
                    End If
                End If
            Next j
            If j > 4 Then Exit Sub
        End If
        
 
        With .Offset(, k).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        ActiveCell.Offset(1, k).Range("A1").Select
    End With
End Sub

Thank you very much!!