Create a MACRO to select specific rows through an iteration and colour them (Excel VBA)

[Aless1275]

Hi, i'm having troubles to do the create an iteration with a for cycle that enables me to select specific row..

So, i have a table with 2 columns, as you can see in the file attached (A with the date, B with the corresponding hour of the day). I would like to create a macro that colours all the row of every new day. I thought to create a for cycle that checks the column B, and if there is a value=1 it means it corresponds to the start of a new day and so it colours the corresponding row. But i don't know how to make also the rows iterate with the for cycle.

In case you would like my to help @mtarler , you would help me to continue the project and allow me to learn more, so that i won't bother you anymore hahaha

Here is the code i created:

Sub color_Rows_Days_test()

Dim rng As Range
Dim cRow As Range

Set rng = Application.Selection

Dim r As Range

For Each r In rng.Rows

Set cRow = rng.Rows(r)

If Cells(cRow.Row, 2).Value = 1 Then

Rows("cRow.Row:cRow.Row").Select

With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With

Else
End If

Next

End Sub

[#2 · Accepted solution]

@Aless1275 

You could use Conditional Formatting:

Select A2:B8761. A2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=$B2=1

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK twice.

If you prefer VBA:

Sub color_Rows_Days_test()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("B1").End(xlDown).Row
    For r = 2 To m Step 24
        With Range("A" & r).Resize(1, 2).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.799981688894314
        End With
    Next r
    Application.ScreenUpdating = True
End Sub

[#3]

Thank you a lot for your answer, it worked well!