Excel VBA loop through every row, except the highlighted rows

[aliceinthewonderland]

Hi guys, I am trying to build a Macro that will do the following:
Select each row and check it against all rest of the rows, based on a criteria / formula. Once criteria is fulfilled, the selected row will be colored in yellow The row causing the selected row to be highlighted will be colored in red.

I want to modify my code so that for the following loop, it will skip those row that are already highlighted. I have my thought of how to do it just that I don't know the code... Can anyone offer some insights / help? Thank you and I appreciate it!

My solution: build an empty list, loop rows that are not in the list. whenever a row is highlighted, it will go into the list.

My current code are like this:

Sub Highlight()
Dim i As Long
Dim m As Long
Dim was As Worksheet

Set ws = ActiveSheet

With ws
For m = .Cells(.rows.Count, 1).End(xlUp).Row To 2 Step -1
For i = .Cells (.Rows.Count, 1).End(xlUp).Row To 2 Step -1
if .Cells (i,1).Value = .Cells (m,1).Value And .Cells (i,2).Value <> .Cells (m,2).
Range(Cells(i,"A"), Cells(i, "G")).Interior.color = vbRed
Range(Cells(m,"A"), Cells(m, "G")).Interior.color = vbYellow
Next i
Next m
End with
End sub

 

[cmowla]

You can do this in at least two main ways.

The first way is to use Range.SpecialCells. (The limitation with this is that your sheet cannot be protected. You have to unprotect the sheet with code and reprotect it if it's protected.)

There are two ways to do this that I know of with Range.SpecialCells:

[1] Assuming that the rows have no other formatting, as an example, color the background (interior) color of the first 10 rows of the active sheet from, say, Columns A through G. Then try a testRowNumber in the below code that is <=10 and one that is > 10. (So you can integrate the below code as a conditional to skip over the already colored rows.)

Sub Skip_Rows_With_Formatting()

Dim sheetName As String
sheetName = ActiveSheet.name

Dim testRowNumber As Long
testRowNumber = 10

Dim firstRowToProcess As Long
firstRowToProcess = 1

Dim lastRowToProcess As Long
lastRowToProcess = 100

With Sheets(sheetName)

    Dim one_Column_In_The_Used_Range As Range
    Set one_Column_In_The_Used_Range = .Range(.Cells(firstRowToProcess, 1), .Cells(lastRowToProcess, 1))
    
    Dim range_With_Formatting As Range
    Set range_With_Formatting = Intersect(one_Column_In_The_Used_Range, .Cells.SpecialCells(xlCellTypeAllFormatConditions))
    
    If Intersect(range_With_Formatting, .Cells(testRowNumber, 1)) Is Nothing Then
        MsgBox "This row does NOT have formatting."
    Else
        MsgBox "This row has formatting."
    End If

End With

End Sub

[2] You can mark off columns as you highlight them by using a helper column. So in this example, it doesn't matter if the rows are formatted or not. You just choose a mark_Off_Column_Number, say Column C, to mark off in your double loop. Type in anything in random rows in that column (Column C in this example). You then pass the testRowNumber to see if the cell at the intersection of the testRowNumber and mark_Off_Column_Number has contents (that you marked it off previously).

Sub Skip_Rows_That_Are_Marked_Off_In_A_Helper_Column()

Dim sheetName As String
sheetName = ActiveSheet.name

Dim testRowNumber As Long
testRowNumber = 7

Dim mark_Off_Column_Number As Integer
mark_Off_Column_Number = 3 'Column C

Dim firstRowToProcess As Long
firstRowToProcess = 1

Dim lastRowToProcess As Long
lastRowToProcess = 100

With Sheets(sheetName)

    Dim mark_Off_Column As Range
    Set mark_Off_Column = .Range(.Cells(firstRowToProcess, mark_Off_Column_Number), .Cells(lastRowToProcess, mark_Off_Column_Number))
    
    Dim empty_Cells_In_Mark_Off_Column As Range
    Set empty_Cells_In_Mark_Off_Column = Intersect(mark_Off_Column, .Cells.SpecialCells(xlCellTypeBlanks))

    If Not Intersect(empty_Cells_In_Mark_Off_Column, .Cells(testRowNumber, mark_Off_Column_Number)) Is Nothing Then
        MsgBox "This row has not been marked off yet."
    Else
        MsgBox "Skip this row.  It's already been marked off."
    End If

End With

End Sub

The second main way to do this is simply to use a helper column to mark off the column as you go (just having VBA type anything into a cell in the highlighted row in the helper column). Then have an excel formula (in just one cell) update with the first blank row number in that column. Have your double loop start from that row number each iteration.

I mentioned both routes, because the second one (although simple), requires the sheet to recalculate. This can slow code down a lot. The second one does not require sheet recalculation, but (again) will require that the sheet is unprotected (at least during execution).