For Loop and Step to Color Cells

Question

I wrote code to color cells in columns 2 and 3 red or light green if both cells on the far right are non-empty. I did a For loop with step to check cells starting from column 99. RED if col 99 and 101 are nonempty, 105 and 107 are nonempty, … LIGHT GREEN if col 99 and 107 are nonempty, 105 and 113 are nonempty,... and so on.
First, I get a 'Next without For' error. And even if that is fixed, the code will not return anything. Please excuse me, I posted a code that does this, and this is a simplified version because the previous one was too wordy.

    For i = 2 To lastrow
        For j = 99 To 167 Step 6    
            If (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 2))) Then    
                Cells(i, 2).Interior.Color = vbRed
                Cells(i, 3).Interior.Color = vbRed        
                    If (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 8))) Then
                        Cells(i, 2).Interior.Color = RGB(146, 208, 80)
                        Cells(i, 3).Interior.Color = RGB(146, 208, 80)        
                    End If
        Next j
    Next i

You're missing an `End If`. Check out this [smart indenter](http://rubberduckvba.com/indentation) to fix your indentation, and it'll become apparent. — BigBen, Nov 14 '19 at 14:15
Doesn't this overwrite what you already colored a cell to when you loop the columns like this? Because you are always in cell `i & column` — Andreas, Nov 14 '19 at 14:25
Yeah, I see where the End If was missing. The code runs but like Andreas said, it overwrites.. How do you prevent that? — cd3091, Nov 14 '19 at 14:39

q0mlm · Accepted Answer · 2019-11-15T01:49:09.553

1

I don't know if this can be a fix for your question:

Sub CheckIfEmpty()


For i = 2 To LastRow

    For j = 99 To 167 Step 6

         If (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 2))) Then
         Cells(i, 2).Interior.Color = vbRed
         Cells(i, 3).Interior.Color = vbRed
         End If
         If (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 8))) Then
         Cells(i, 2).Interior.Color = RGB(146, 208, 80)
         Cells(i, 3).Interior.Color = RGB(146, 208, 80)
         End If
    Next j
Next i

'Edited part : YELLOW

For i = 2 To LastRow
    For j = 99 To 167 Step 6
       For x = 0  to 60 Step 6 
         If (Not IsEmpty(ActiveSheet.Cells(i, j)) And Not IsEmpty(Cells(i, j + 14 +x))) Then
         Cells(i, 2).Interior.Color = vbYellow
         Cells(i, 3).Interior.Color = vbYellow
         End If
      Next x
   Next j
Next i
End Sub

NOTES:

A) This code, which is the most adherent to your proposal example, checks if any of the one-spaced or seven-spaced separated cells are nonempty. Meaning, in the one-spaced case, it checks if (99 AND 101) OR (105 AND 107) OR...(165 AND 167) are nonempty

B) If both cases are validated the color will be established in this order :

Column j with higher value (Example : if you have the following non-empty cells 99 - 105 - 107 the color will be Red)
If Column j has the same value the color will be green. (Example : if you have the following non-empty cells 99 - 105 - 107 - 113 the color will be Green)

C) Remember to follow this guideline in defining your LastRow

edited Nov 15 '19 at 01:49

answered Nov 14 '19 at 15:41

q0mlm

313
1
3
10

The code in your answer works (is it the same one as I posted?) Can you also write a code for yellow? I am doing in the same way but it's wordy with syntax errors. This time it's 14, 20, 26, 32, 38, 44 (increments of 6 starting at 14). – cd3091 Nov 14 '19 at 17:31
I have add an End If at the end of the first IF conditional. – q0mlm Nov 14 '19 at 18:22
Yeah, I fixed that before. But then it overwrote the red, while trying again after I saw your post, it did not. Weird. – cd3091 Nov 14 '19 at 18:24
I've edited the post to add the loop for yellow. Can you try to explain better what the "yellow part" should check? I believe the overwriting problem you are reporting is a flaw in the design of your code. As I said if there are very far nonempty cells that follow the RED pattern, say cells 165 and 167 are non empty, and nearer nonempty cells that follow the GREEN pattern, say cells 105 and 113, the result of your code will be red. Let me know about the yellow code and about the functioning of the loop in general. – q0mlm Nov 14 '19 at 18:37
There are ways to avoid the overwriting problem, however you should clarify what your code should achieve : you want your code to detect first and foremost if there is a RED pattern or a GREEN pattern? If in a row there is a concurrence of a RED and GREEN pattern, which of the two you want to be highlighted? Notes A and B are about this. I would suggest to Run the Macro step by step with F8 so you can see all the changes from RED to GREEN and vice versa, and you can get a first-hand understanding of what your code does and when. – q0mlm Nov 14 '19 at 18:52
Sounds good. Thank you for the detailed answers. For the YELLOW, it also starts at 99. However it jumps 14 then increases by 6. – cd3091 Nov 14 '19 at 18:53
So for the YELLOW j should take the following values : 99_113_119_125_131_137_143_149_155_161_167, is thst correct? What pattern should YELLOW check? For example: two-spaced separated non-empty cells (j+3) like 99 and 102, 113 and 116,.....,167 and 170? – q0mlm Nov 15 '19 at 01:00
Yes to your 1st question. YELLOW checks (99, 113), (99, 119), (99, 125),..., (105, 119), (105, 125), (105, 131),..., (111, 125), (111, 131), .... It's more complicated than red and green.. – cd3091 Nov 15 '19 at 01:19
I've edited the code taking into account your last comment. Take a look at the Macro step by step and give a thought about the overwriting problem. I believe that with a clearer idea of what you want to achieve we could redesign the code in order for it to be more synthetic and efficient. – q0mlm Nov 15 '19 at 01:53

For Loop and Step to Color Cells

1 Answers1