SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2829986%22%20slang%3D%22en-US%22%3ECreate%20a%20MACRO%20to%20select%20specific%20rows%20through%20an%20iteration%20and%20colour%20them%20(Excel%20VBA)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2829986%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20i'm%20having%20troubles%20to%20do%20the%20create%20an%20iteration%20with%20a%20for%20cycle%20that%20enables%20me%20to%20select%20specific%20row..%3C%2FP%3E%3CP%3ESo%2C%20i%20have%20a%20table%20with%202%20columns%2C%20as%20you%20can%20see%20in%20the%20file%20attached%20(A%20with%20the%20date%2C%20B%20with%20the%20corresponding%20hour%20of%20the%20day).%20I%20would%20like%20to%20create%20a%20macro%20that%20colours%20all%20the%20row%20of%20every%20new%20day.%20I%20thought%20to%20create%20a%20for%20cycle%20that%20checks%20the%20column%20B%2C%20and%20if%20there%20is%20a%20value%3D1%20it%20means%20it%20corresponds%20to%20the%20start%20of%20a%20new%20day%20and%20so%20it%20colours%20the%20corresponding%20row.%20But%20i%20don't%20know%20how%20to%20make%20also%20the%20rows%20iterate%20with%20the%20for%20cycle.%3C%2FP%3E%3CP%3EIn%20case%20you%20would%20like%20my%20to%20help%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%2C%20you%20would%20help%20me%20to%20continue%20the%20project%20and%20allow%20me%20to%20learn%20more%2C%20so%20that%20i%20won't%20bother%20you%20anymore%20hahaha%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20code%20i%20created%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESub%20color_Rows_Days_test()%3C%2FP%3E%3CP%3EDim%20rng%20As%20Range%3CBR%20%2F%3EDim%20cRow%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20rng%20%3D%20Application.Selection%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDim%20r%20As%20Range%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Each%20r%20In%20rng.Rows%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20cRow%20%3D%20rng.Rows(r)%3C%2FP%3E%3CP%3EIf%20Cells(cRow.Row%2C%202).Value%20%3D%201%20Then%3CBR%20%2F%3E%3CBR%20%2F%3ERows(%22cRow.Row%3AcRow.Row%22).Select%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20Selection.Interior%3CBR%20%2F%3E.PatternColorIndex%20%3D%20xlAutomatic%3CBR%20%2F%3E.ThemeColor%20%3D%20xlThemeColorAccent2%3CBR%20%2F%3E.TintAndShade%20%3D%200.799981688894314%3CBR%20%2F%3E.PatternTintAndShade%20%3D%200%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EElse%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ENext%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2829986%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2830030%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20MACRO%20to%20select%20specific%20rows%20through%20an%20iteration%20and%20colour%20them%20(Excel%20VBA)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2830030%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1176875%22%20target%3D%22_blank%22%3E%40Aless1275%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20use%20Conditional%20Formatting%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20A2%3AB8761.%20A2%20should%20be%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20click%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3CBR%20%2F%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3CBR%20%2F%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%3D%24B2%3D1%3C%2FP%3E%0A%3CP%3EClick%20Format...%3CBR%20%2F%3EActivate%20the%20Fill%20tab.%3CBR%20%2F%3ESelect%20a%20highlight%20color.%3CBR%20%2F%3EClick%20OK%20twice.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20prefer%20VBA%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20color_Rows_Days_test()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20m%20%3D%20Range(%22B1%22).End(xlDown).Row%0A%20%20%20%20For%20r%20%3D%202%20To%20m%20Step%2024%0A%20%20%20%20%20%20%20%20With%20Range(%22A%22%20%26amp%3B%20r).Resize(1%2C%202).Interior%0A%20%20%20%20%20%20%20%20%20%20%20%20.ThemeColor%20%3D%20xlThemeColorAccent2%0A%20%20%20%20%20%20%20%20%20%20%20%20.TintAndShade%20%3D%200.799981688894314%0A%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20Next%20r%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2831416%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20MACRO%20to%20select%20specific%20rows%20through%20an%20iteration%20and%20colour%20them%20(Excel%20VBA)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2831416%22%20slang%3D%22en-US%22%3EThank%20you%20a%20lot%20for%20your%20answer%2C%20it%20worked%20well!%3C%2FLINGO-BODY%3E
Occasional Contributor

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 Replies
best response confirmed by Aless1275 (Occasional Contributor)
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

 

 

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