If a value matches in columns in any two rows, I want to highlight only the value in the row that comes second in Excel using VBA. How?

Question

I am trying to create a calendar in Excel using VBA. It looks as shown:

Column B is the month column. The month starts on column B on the week (row) in which the first day of the month occurs. Example, February starts in row 6 although there are some last days of January in the same row.

I want to highlight the holidays. For example, I have holidays on January 1, which is highlighted in cyan color. I have written a code by defining an two-dimensional array object my_holidays(x,y). x refers to the month and y refers to the day based on following:

I wrote a code which searches for month in column B and the day on columns C to I, and highlights if it finds the match:

Dim last_row As Integer
last_row = ws1.Range("B1").End(xlDown).row

Dim row As Integer
Dim column As Integer

For row = 1 To last_row
    For column = 3 To 9
        For i = 1 To num_holidays
            
            If ws1.Range("B" & row).Value = my_holidays(i, 1) And ws1.Cells(row, column).Value = my_holidays(i, 2) Then
                ws1.Cells(row, column).Interior.Color = vbCyan
            End If
            
            
        Next i
        
        
    Next column
    
Next row

My problem is that if I have holiday on days such as March 29, it highlights two rows here 10 and 14 as shown in the screenshot above. However, the 29 day in row 10 corresponds to February.

How can I modify my code such that if the holiday corresponds to two rows for a month, it should select the second row using VBA in Excel?

Answer 1

• Introduce the bFirstRow variable to distinguish the first row of each month.

• Fix the logic to mark 27 Feb.

• Loop through data from the 2nd row For row = 2, otherwise Cells(row - 1, 2) raises error.

    Dim row As Integer
    Dim column As Integer
    Dim bFirstRow As Boolean
    Dim sMth As String
    For row = 2 To last_row
        If ws1.Cells(row, 2) <> ws1.Cells(row - 1, 2) Then
            bFirstRow = True
        Else
            bFirstRow = False
        End If
        For column = 3 To 9
            If bFirstRow And ws1.Cells(row, column) > 20 Then
                sMth = ws1.Cells(row - 1, 2)
            Else
                sMth = ws1.Cells(row, 2)
            End If
            For i = 1 To num_holidays
                If sMth = my_holidays(i, 1) And ws1.Cells(row, column).Value = my_holidays(i, 2) Then
                    ws1.Cells(row, column).Interior.Color = vbCyan
                End If
            Next i
        Next column
    Next row

---

Update:

Question: If he had used Feb on row 10, I believe this would fail to mark 02 Mar.

    Dim row As Integer
    Dim column As Integer
    Dim sMth As String
    For row = 2 To last_row
        For column = 3 To 9
            sMth = ws1.Cells(row, 2)
            If ws1.Cells(row, 2) <> ws1.Cells(row - 1, 2) Then
                If ws1.Cells(row, column) > 21 Then sMth = ws1.Cells(row - 1, 2)
            ElseIf ws1.Cells(row, 2) <> ws1.Cells(row + 1, 2) Then
                If ws1.Cells(row, column) < 7 Then sMth = ws1.Cells(row + 1, 2)
            End If
            For i = 1 To num_holidays
                With ws1.Cells(row, column)
                    If sMth = my_holidays(i, 1) And .Value = my_holidays(i, 2) Then
                        .Interior.Color = vbCyan
                    End If
                End With
            Next i
        Next column
    Next row