Error with FOR/IF statement

[andrewb90]

Hello all,

I know I have an issue with the statement, but I'm not entirely sure how to correct it. Any guidance would be greatly appreciated.

    Dim r As Long    For r = 4 To 50
        If r <> 6 And r <> 45 Then
[COLOR=#ff0000]            If Range("C" & r).Value = "Front" Then Range("C" & r: "G" & r).select[/COLOR]
            With Selection
                .HorizontalAlignment = xlGeneral
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = True
            End With
   
        End If
    Next r

Red is the problem. What I am trying to do is if "C" matches that value, then C:G will be selected. Additionally If anybody can assist me with putting multiple values as a search instead of just "front", i'd like to add several others, I'm just not sure how.

 

[andrewb90]

So here is what I have now:

    Dim r As Long    Dim myRange As Range
    
    For r = 4 To 50
        If (r <> 6) And (r <> 47) Then
            If (Cells(r, "C") = "Front") Or (Cells(r, "C") = "Back") Or _
            (Cells(r, "C") = "Maintenance") Or (Cells(r, "C") = "Managers") Then
                Set myRange = Range(Cells(r, "C"), Cells(r, "G"))
                Set myForm = Range(Cells(r, "G"))
                With myRange
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = True
                End With
                With myRange.Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With myRange.Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                'With myForm
                   ' .value = "formula here"


                    
            End If
        End If
    Next r

So with my 4-50 range if "C" has one of my words then the cells will merge. However If they don't have that value, I would want to see if "C" is blank and if it is, then continue, and if it's not, then put a formula in "G" of that row. I feel like I could come up with everything code wise, but how to make it look at several IF's in one row.

I hope I'm making sense, from time to time, something that is jumbling around in my head, comes out just as jumbled...

 

[Joe4]

You should be able to add an ELSE statement just before the first End If, i.e.

        Else
            'Check to see if column C is something else, but not blank
            If Cells(r,"C")<>"" Then
            '   add formula to column G here
            End If
        End If
    End If
Next r

 

[andrewb90]

awesome! thank you!