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 to fix your indentation, and it'll become apparent. — BigBen
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
Yeah, I see where the End If was missing. The code runs but like Andreas said, it overwrites.. How do you prevent that? — cd3091

q0mlm q0mlm · Accepted Answer · 2019-11-14T15:41:35

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

For Loop and Step to Color Cells

1 Answers