Skipping cells that are coloured

Question

I have a loop, where I want to skip cells that are coloured.

For i = 1 To Count
    Do While ActiveCell.Offset(0, i).Interior.ColorIndex = 15
        i = i + 1: Count = Count + 1
    Loop

    With ActiveCell.Offset(0, i).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
Next i

It works, however the initial count variable is not updated. Thus, if I have 10 and there are 2 skips, the i value is increased and that works, still the count remains at 10, even though the variable says 12. It appears as though increasing the count variable does not increment the For loop. I cannot take 1 away from the i variable because that leads to the activecell.offset being impacted.

Am I correct in saying that you are trying to find the last cell that has a color index of 15 and then changing the color of the next cell? — Brian, Aug 11 '16 at 10:22
A for loop is precompiled you can't alter it after you start it. You need to use a While loop to be able to increment count — DragonSamu, Aug 11 '16 at 10:32
Possible duplicate of [Change length of For loop while in the loop](http://stackoverflow.com/questions/19409644/change-length-of-for-loop-while-in-the-loop) — DragonSamu, Aug 11 '16 at 10:37

score 3 · Accepted Answer · answered Aug 11 '16 at 11:07

3

Why use .Offset at all? Is this what you are trying? This way you can skip the colored cells as well.

Dim col As Long, rw As Long, i As Long

col = ActiveCell.Column
rw = ActiveCell.Row

For i = 1 To Count
    With Cells(rw, col + i)
        If .Interior.ColorIndex <> 15 Then
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent3
                .TintAndShade = -0.249977111117893
                .PatternTintAndShade = 0
            End With
        End If
    End With
Next i

answered Aug 11 '16 at 11:07

Siddharth Rout

147,039
17
206
250

Siddhart, as usual you provide a great answer! The reason I had to keep the offset, if because there can be a remainder, which needs to be in a different colour. In essence, I am creating a planner which takes numbers and divides them by 7.5 (a working day) and then paints by offset by the answer from the division, while the remainder will be tinted a different colour. See below for my convoluted answer. – Lowpar Aug 11 '16 at 13:30
I am sure, you still do not need the `.Offset` :) – Siddharth Rout Aug 11 '16 at 13:33

score 0 · Answer 2 · answered Aug 11 '16 at 13:48

Probably very inefficient code, but there are not a lot of rows. Remember the idea is that the column has a date, if that column is Saturday or Sunday, i.e., coloured grey, then the code should skip those cells and but not subtract them from the overall counter.

If Not IsEmpty(y.Value) And IsNumeric(y.Value) And y.Value >= 7.5 Then
With ActiveCell.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent3
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With
Col = y.Value - 7.5
Col = Col / 7.5


Count = Left(Col, Len(Col) - InStr(1, Col, ".") + 1)
y = 0
For i = 1 To Count


Do While ActiveCell.Offset(0, i).Interior.ColorIndex = 15
ActiveCell.Offset(0, 1).Select
y = y + 1
Loop
With ActiveCell.Offset(0, i).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent3
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
End With
Next i

ActiveCell.Offset(0, -y).Select
ActiveCell.Offset(0, i + y).Select
Do While ActiveCell.Interior.ColorIndex = 15
ActiveCell.Offset(0, 1).Select
Loop
Co = Right(Col, Len(Col) - InStr(1, Col, "."))
If Len(Co) > 2 Then
Co = Mid(Co, 1, InStr(1, Col, ".")) & "." & Mid(Co, InStr(1, Col, ".") + 1, Len(Co) - InStr(1, Col, "."))
End If
If Co = 0 Then
ElseIf Co >= 0.1 And Co <= 25 Then
With ActiveCell.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -4.99893185216834E-02
    .PatternTintAndShade = 0
End With
ElseIf Co >= 26 And Co <= 49 Then
With ActiveCell.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
End With
ElseIf Co >= 5 And Co <= 74 Then
With ActiveCell.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
End With
ElseIf Co >= 75 And Co <= 99 Then
With ActiveCell.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -4.99893185216834E-02
    .PatternTintAndShade = 0
End With
End If
End If

Next y

Skipping cells that are coloured

2 Answers2