Looping through Rows to Validate their Content with VBA in Excel

I have a xls-file which contains several date-columns. I change the background-color of the rows depending on the content of the row.

Here is my problem: When i enter a invalid value on row 10 and 100. Every row from 7 to 100 is getting checked and 10th and 100th row is colored red. (I start at row 7 because there are some headerrows). When i now delete the text in the 100th row. The loop only goes to the 10th row and ends. Obviously because 10 is the last used row. But the 100th row is still colored in red.
Thats because my loop colors the rows wihtout content to no color. And i only loop through the columns that have values.

Any ideas to solve my problem?

Here is my VB-Code:

Private Sub Worksheet_Change(ByVal Target As Range)
CheckAllDateCells
End Sub
Private Sub CheckAllDateCells()
CheckDateCellsForColumn "T", 7
CheckDateCellsForColumn "V", 7
CheckDateCellsForColumn "X", 7
CheckDateCellsForColumn "Y", 7
CheckDateCellsForColumn "AI", 7
CheckDateCellsForColumn "AJ", 7
CheckDateCellsForColumn "AK", 7
CheckDateCellsForColumn "AL", 7
CheckDateCellsForColumn "AM", 7
CheckDateCellsForColumn "AN", 7
CheckDateCellsForColumn "AO", 7
CheckDateCellsForColumn "AP", 7
End Sub
Private Sub CheckDateCellsForColumn(column As String, firstRowIndex As Long)
For i = firstRowIndex To Me.Range(column & Me.Rows.Count).End(xlUp).row
CheckDateCell i, Me.Range(column & 1).column
Next i
End Sub
Private Sub CheckDateCell(ByVal rowIndex As Long, ByVal columnIndex As Long)
If Not IsEmpty(Cells(rowIndex, columnIndex).value) Then
If IsDate(Cells(rowIndex, columnIndex).value) Then
If Cells(rowIndex, columnIndex).value Like "##.##.####" Then
Cells(rowIndex, columnIndex).Interior.ColorIndex = 10
Else
Cells(rowIndex, columnIndex).Interior.ColorIndex = 6
End If
Else
Cells(rowIndex, columnIndex).Interior.ColorIndex = 3
End If
Else
Cells(rowIndex, columnIndex).Interior.ColorIndex = 0
End If
End Sub


Maybe i can achive this wihtout using vb? only conditional formatting?

conditional formatting just gives me the possibillity to color cells on specific criteria, like date is yesterday or date is from this month etc. But i can not select a daterange by myself like date is between 01.01.1899 and 01.01.2999…

Please remember to check a tag’s description before adding it to your question. VB.NET is not the same thing as VBA.
– Visual Vincent
18 hours ago

If I understand correctly, you basically need to just reset all the cells to “No Colour" – if it’s a viable quick fix, just say something like cells(1, column).EntireColumn.Interior.Color = xlNone at the very start? Or even just stick a +100 on your For i = firstRowIndex To Me.Range(column & Me.Rows.Count).End(xlUp).row line to make it loop 100 rows past the last row
– jamheadart
18 hours ago

cells(1, column).EntireColumn.Interior.Color = xlNone

+100

For i = firstRowIndex To Me.Range(column & Me.Rows.Count).End(xlUp).row

I’m with @jamheadart. I’d recommend resetting your entire column to no color at the beginning of the macro used to shade the invalid rows.
– TempleGuard527
18 hours ago

cells(1, column).EntireColumn.Interior.ColorIndex = 0; works, But it takes a little bit too long. I have to wait like 2 seconds. This makes a bad workflow. For the first, i will stick to the +100 on my loop if i will not find a better solution.
– Olli
18 hours ago

You have several date-columns and want to change the background-color of the rows, so what do you want to happen to a row that contains both a valid and an invalid format?
– pnuts
18 hours ago

1 Answer
1

You can stick + 100 to your loop count so that it goes 100 rows past the last row that contains data (but this looping through empty cells so is a bit “hacky")

For i = firstRowIndex To Me.Range(column & Me.Rows.Count).End(xlUp).row + 100

For i = firstRowIndex To Me.Range(column & Me.Rows.Count).End(xlUp).row + 100

You can reset the entire column right at the start (but this is proving to be slow – EntireColumn is a big object!):

cells(1, column).EntireColumn.Interior.Color = xlNone

cells(1, column).EntireColumn.Interior.Color = xlNone

Best idea is a combo of both – reset the Range you’re doing in one go using the last row + 100 so no looping further than required (100 being a sortof arbitrary number assuming you don’t delete more than 100 rows before running, could easily be 1000 which is still tiny compared to “EntireColumn")

Private Sub CheckDateCellsForColumn(column As String, firstRowIndex As Long)
Dim lROW As Long: lROW = Me.Range(column & Me.Rows.Count).End(xlUp).Row
Me.Range(column & firstRowIndex & ":" & column & lROW + 100).Interior.Color = 0
For i = firstRowIndex To lROW
CheckDateCell i, Me.Range(column & 1).column
Next i
End Sub


By clicking “Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.