Excel VBA loop through every row, except the highlighted rows

Joined
Sep 21, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
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.)
VBA Code:
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).

VBA Code:
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).
 

Forum statistics

Threads
1,147,823
Messages
5,743,405
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top