Applying conditional formatting to variable range

Question

I want this macro to select range C6:N6, apply a format condition, step 7 and so on until the end of my list. When i execute it, it bings me an error regarding the objects.

I think my problem is on the following part:

For i = 6 To lastRow Step 7
    Range(C & i, N & i).Select

This way I want to make the ranges variable. Below is my code:

Sub test1()
' Coluna C

Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row ' Pega ultima celula com valores na coluna C

For i = 6 To lastRow Step 7
    Range(C & i, N & i).Select 'what am i missing?

         With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False


Next i


End Sub

Your code is considering both `C` and `N` as variables, when it should consider them as literals. For that you need to enclose them in double quotes(`"`). Try to replace them to `Range("C" & i, "N" & i).Select`. And why not working with the entire range at once, instead? you could remove the `For` and just work with something like `Range("C6", "N" & lastRow).Select` — Victor Moraes, Sep 15 '16 at 17:34
@VictorMoraes - actually, I suggest Serveira [avoid `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) completely. It will save many headaches in the future. (But I agree with your overall point to work with the range at once). — BruceWayne, Sep 15 '16 at 17:46
@BruceWayne I completely agree with you and I'm actually working on some code to add a proper answer to this post, without using the `Select`, though I understand that OP is not working with entire range, since he is using `Step 7` in the loop — Victor Moraes, Sep 15 '16 at 17:49

score 0 · Accepted Answer · edited May 23 '17 at 12:01

Instead of keeping this into comments, here is an answer to your problem.
Your code is considering both C and N as variables, when it should consider them as literals. For that, you need to enclose them in double quotes("). So instead of

Range(C & i, N & i).Select

You need to do

Range("C" & i, "N" & i).Select

There is also one problem with your lastRow assignment. Based on your comments you want to get the last cell with values from column C, which is column 3, but you are passing the wrong index for colum in here:

lastRow = Cells(Rows.Count, 1).End(xlUp).Row ' Pega ultima celula com valores na coluna C

Instead, you should be using

lastRow = Cells(Rows.Count, 3).End(xlUp).Row ' <-- column C is index 3

I'm adding this because you might find issues if your column A is not properly filled or doesn't have the same amount of data as the desired column C

Besides, as greatly suggested by BruceWayne (off topic: nice username, by the way), you should avoid .Select completely in your code. You can work with With blocks, similarly to what you are already doing, and also use proper reference to your cells and ranges, instead of relying on the active worksheet. With that, you would end up with a code more like the following

Sub test1()
' Coluna C

    Dim lastRow As Long
    With ThisWorkbook.Worksheets("YourSheetName")
        lastRow = .Cells(.Rows.Count, 3).End(xlUp).row ' Pega ultima celula com valores na coluna C

        For i = 6 To lastRow Step 7
            With .Range("C" & i, "N" & i)

                With .Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
                    Formula1:="=0"
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                With .FormatConditions(1).Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                End With
                .FormatConditions(1).StopIfTrue = False
            End With

        Next i
    End With

End Sub

That looks great, thank you! As it's been only one week since i started leaning to code, it's kind of inevitable for me to use `.Select`, it looks so much easier, ahah. As i see, it's best to avoid it completely from the beggining, so I'll have a detailed look at your code and see how it is improved from mine. Many thanks! — Serveira, Sep 15 '16 at 20:22

Applying conditional formatting to variable range

1 Answers1