Oct 09 2021 09:29 AM
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
Oct 09 2021 09:49 AM
SolutionYou 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
Oct 10 2021 07:43 AM
Oct 09 2021 09:49 AM
SolutionYou 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