VBA to identify colored text in every 5 cells of a column

michaelp0628 · Jan 14th 2023

Hello,

I have a file which starts data in Cell E8 and continues down and to the right in varying columns in rows.

I created a macro to color certain data in the file yellow

I am now writing a macro to look at cells E8-E12, and if the cells contain 3 yellow colored cells, all 5 cells turn red.

This will continue through E13-E17 and also loop through all relevant columns that contain data.

In short starting at E8 it should check every 5 cells in a row for 3 colored data points. If there are three colored data points all 5 cells turn red.

I don't understand how to make my For statement jump down 5 columns for each loop. Could I have some advice?

Code

Sub FailCheck()


Dim Q As Variant
Dim ColorCounter As Integer
Dim lastrow As Long
Dim lastcolumn As Integer
Dim PCM As Integer
PCM = 5
lastrow = Range("E" & Rows.count).End(xlUp).Row
' Find the count of rows '


lastcolumn = Cells(1, Columns.count).End(xlToLeft).Column
' Find the count of columns '
For i = 5 To lastcolumn
For Each Q In Range("E & (8+5(i-4)):E" & lastrow).Offset(, i - 5)
If Q.Interior.Color = vbYellow Then
ColorCounter = ColorCounter + 1
If ColorCounter > 2 Then
Range("E8:E12").Interior.Color = vbRed
End If
MsgBox (ColorCounter)
End If
Next Q
Next i
End Sub

Display More

**KjBox** · Jan 14th 2023

Try changing

Code

For i = 5 To lastcolumn

to

Code

For i = 5 To lastcolumn Step 5

michaelp0628 · Jan 16th 2023

Above is close to what I want but, the Step is through the columns. I would like the step to be through the rows.

I know I need a similar function to step "down" but I'm not sure how to write it.

**Carim** · Jan 16th 2023

Hi,

KjBox gave you the solution ....

Below an example for a double loop :

Code

 ' Adjust to your own situation
    ' Variable i For Rows     with a Step of 5
    ' Variable j For Columns  with a Step of 3
    
    For i = 1 To lastRow Step 5
        For j = 1 To LastColumn Step 3
        '
        ' Your macro / actions
        '
        Next j
    Next i

Display More

Hope this will help

michaelp0628 · Jan 16th 2023

Thank you, I'm not the best at VBA so I think I was having a hard time understanding it.

I ended up making a double loop and used offset to accomplish the same thing. Although the Step command seem simpler.

Code

Sub FailCheck()


Dim Q As Variant
Dim ColorCounter As Integer
Dim lastrow As Long
Dim lastcolumn As Integer
Dim PCM As Integer
PCM = 5
lastrow = Range("E" & Rows.count).End(xlUp).Row
' Find the count of rows '


lastcolumn = Cells(1, Columns.count).End(xlToLeft).Column
' Find the count of columns '
For j = 0 To lastrow / PCM
For i = 5 To lastcolumn
For Each Q In Range("E8:E12").Offset(5 * (j), i - 5)
If Q.Interior.Color = vbYellow Then
ColorCounter = ColorCounter + 1
If ColorCounter > 2 Then
Range("E8:E12").Offset(5 * (j), i - 5).Interior.Color = vbRed
ColorCounter = Empty
End If


End If
Next Q
Next i
Next j
End Sub

Display More

**Carim** · Jan 16th 2023

Congratulations on reaching your own solution !!!

We are, all of us, embarked on a continuous (and fun) Learning journey ...!!!

VBA to identify colored text in every 5 cells of a column

Participate now!

Share

Similar Threads

VBA to add rows between dates and populate other columns

VBA code to prevent typing in Cells and Columns

VBA Copy rows to another sheet due to cell value from dropdown list

Users Viewing This Thread