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

Question

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

hansvogelaar · Accepted Answer

Aless1275&nbsp;
You could use Conditional Formatting:
&nbsp;
Select A2:B8761. A2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting &gt; 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.
&nbsp;
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" &amp; r).Resize(1, 2).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.799981688894314
        End With
    Next r
    Application.ScreenUpdating = True
End Sub
&nbsp;
&nbsp;

Forum Discussion

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

2 Replies

Resources