Color non-adjacent cells that match criteria

Question

I use the below code to color the cells in column K and Z that match the criteria; but it colors all cells between K and Z. To fix, I use the last line of code to remove the color in columns L thru Y. Is there a way to modify the line of code that starts with "Range" to only color cells K and Z that match the criteria?

Sub ColrCls()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("K" & .Rows.Count).End(xlUp).Row

        For i = 2 To lRow
            If .Cells(i, 11).Value = "Non Sen" And .Cells(i, 26).Value = "N/A" Then
            Range(.Cells(i, 11), .Cells(i, 26)).Interior.ColorIndex = 6
            End If
        Next i

        Columns("L:Y").Interior.ColorIndex = xlNone
    End With
End Sub

In my opinion you don't even need Vba. Did you try conditional formatting? — cyboashu, Jul 13 '16 at 01:59
I would prefer vba, because I will be adding 4 or 5 additional IF statements. Clearing columns L thru Y is sloppy, but I don't know how to fix the Range line of code. — GMalc, Jul 13 '16 at 02:03

score 2 · Accepted Answer · edited May 23 '17 at 10:28

2

You are specifying the Range.Parent property in your With ... End With statement but ignoring it when it is most important¹.

Sub ColrCls()

    Dim ws As Worksheet
    Dim lRow As Long, i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("K" & .Rows.Count).End(xlUp).Row

        For i = 2 To lRow
            If .Cells(i, 11).Value = "Non Sen" And .Cells(i, 26).Value = "N/A" Then
                .Range("K" & i & ", Z" & i).Interior.ColorIndex = 6
            Else
                .Range("K" & i & ", Z" & i).Interior.Pattern = xlNone
            End If
        Next i
    End With
End Sub

A Range object to Union discontiguous cells could be one of the following.

.Range("K5, Z5")
Union(.Cells(5, "K"), .Cells(5, "Z"))

In the example above, I've concatenated together a string like the first of these two examples.

¹ _{See Is the . in .Range necessary when defined by .Cells? for an earnest discussion on this subject.}

edited May 23 '17 at 10:28

Community

1
1

answered Jul 13 '16 at 02:10

btw, you might need an `Else` to clear the interior colorindex if K and Z do not meet the conditions but did on a previous run through. In this case you would use `.Range("K" & i & ", Z" & i).Interior.Pattern = xlNone`. – Jul 13 '16 at 02:24
Thanks Jeeped, I will test your code tomorrow at work. I will get back with you tomorrow with what i find out. I will also read the information about .Range. – GMalc Jul 13 '16 at 02:37
I check your fix on some mock data and it work perfectly, Thank you. – GMalc Jul 13 '16 at 02:54
For *'4 or 5 additional IF statements'* you may want to swtich the method to a [Select Case statement](https://msdn.microsoft.com/en-us/library/office/gg278665.aspx). See **[Conditional Formatting VBA](http://stackoverflow.com/questions/38319443/conditional-formatting-vba/38319605#38319605)**. – Jul 13 '16 at 03:32

score 2 · Answer 2 · answered Jul 13 '16 at 02:27

2

You could replace

Range(.Cells(i, 11), .Cells(i, 26)).Interior.ColorIndex = 6

with

.Cells(i, 11).Interior.ColorIndex = 6
.Cells(i, 26).Interior.ColorIndex = 6

answered Jul 13 '16 at 02:27

YowE3K

23,852
7
26
40

your input worked fine, i just prefer Jeeped's input. Thanks for the input. – GMalc Jul 13 '16 at 02:59

Color non-adjacent cells that match criteria

2 Answers2