looking for vba codes for subtotal based on dates

L

learning_grexcel

Active Member
Joined
Jan 29, 2011
Messages
319
As shown below, I want to do subtotal on column "weekly total" every Friday for values on total columns.


And also i want to highlight the color the subtotal cells.

It should be done through the columns (E,K,Q,W,AC)


I'm looking for VBA code to achieve this.


Excel 2010
ABCDEFGHIJK
DateQty1Qty2TotalWeekly TotalDateQty1Qty2TotalWeekly Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1-Aug-12[/TD]
[TD="align: right"]4559[/TD]
[TD="align: right"]2893[/TD]
[TD="align: right"]7452[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-Aug-12[/TD]
[TD="align: right"]2721[/TD]
[TD="align: right"]2983[/TD]
[TD="align: right"]5704[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2-Aug-12[/TD]
[TD="align: right"]1161[/TD]
[TD="align: right"]2446[/TD]
[TD="align: right"]3607[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2-Aug-12[/TD]
[TD="align: right"]3479[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]3659[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #E6B8B7, align: right"]3-Aug-12[/TD]
[TD="align: right"]1280[/TD]
[TD="align: right"]882[/TD]
[TD="align: right"]2162[/TD]
[TD="bgcolor: #E6B8B7, align: right"]13221[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E6B8B7, align: right"]3-Aug-12[/TD]
[TD="align: right"]4369[/TD]
[TD="align: right"]1582[/TD]
[TD="align: right"]5951[/TD]
[TD="bgcolor: #E6B8B7, align: right"]15314[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4-Aug-12[/TD]
[TD="align: right"]4096[/TD]
[TD="align: right"]1929[/TD]
[TD="align: right"]6025[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4-Aug-12[/TD]
[TD="align: right"]4679[/TD]
[TD="align: right"]3865[/TD]
[TD="align: right"]8544[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5-Aug-12[/TD]
[TD="align: right"]3337[/TD]
[TD="align: right"]518[/TD]
[TD="align: right"]3855[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5-Aug-12[/TD]
[TD="align: right"]4270[/TD]
[TD="align: right"]3572[/TD]
[TD="align: right"]7842[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6-Aug-12[/TD]
[TD="align: right"]2061[/TD]
[TD="align: right"]3366[/TD]
[TD="align: right"]5427[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6-Aug-12[/TD]
[TD="align: right"]4146[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]4207[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7-Aug-12[/TD]
[TD="align: right"]3757[/TD]
[TD="align: right"]3759[/TD]
[TD="align: right"]7516[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7-Aug-12[/TD]
[TD="align: right"]212[/TD]
[TD="align: right"]2036[/TD]
[TD="align: right"]2248[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8-Aug-12[/TD]
[TD="align: right"]3629[/TD]
[TD="align: right"]3876[/TD]
[TD="align: right"]7505[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8-Aug-12[/TD]
[TD="align: right"]3145[/TD]
[TD="align: right"]4619[/TD]
[TD="align: right"]7764[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9-Aug-12[/TD]
[TD="align: right"]3426[/TD]
[TD="align: right"]4657[/TD]
[TD="align: right"]8083[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9-Aug-12[/TD]
[TD="align: right"]4966[/TD]
[TD="align: right"]421[/TD]
[TD="align: right"]5387[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #E6B8B7, align: right"]10-Aug-12[/TD]
[TD="align: right"]4293[/TD]
[TD="align: right"]2336[/TD]
[TD="align: right"]6629[/TD]
[TD="bgcolor: #E6B8B7, align: right"]45040[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E6B8B7, align: right"]10-Aug-12[/TD]
[TD="align: right"]1826[/TD]
[TD="align: right"]3309[/TD]
[TD="align: right"]5135[/TD]
[TD="bgcolor: #E6B8B7, align: right"]41127[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11-Aug-12[/TD]
[TD="align: right"]560[/TD]
[TD="align: right"]1794[/TD]
[TD="align: right"]2354[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11-Aug-12[/TD]
[TD="align: right"]199[/TD]
[TD="align: right"]2841[/TD]
[TD="align: right"]3040[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12-Aug-12[/TD]
[TD="align: right"]4314[/TD]
[TD="align: right"]1861[/TD]
[TD="align: right"]6175[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12-Aug-12[/TD]
[TD="align: right"]630[/TD]
[TD="align: right"]3387[/TD]
[TD="align: right"]4017[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13-Aug-12[/TD]
[TD="align: right"]2072[/TD]
[TD="align: right"]3086[/TD]
[TD="align: right"]5158[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13-Aug-12[/TD]
[TD="align: right"]4381[/TD]
[TD="align: right"]223[/TD]
[TD="align: right"]4604[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14-Aug-12[/TD]
[TD="align: right"]4285[/TD]
[TD="align: right"]3760[/TD]
[TD="align: right"]8045[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14-Aug-12[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]1705[/TD]
[TD="align: right"]1909[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15-Aug-12[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]4996[/TD]
[TD="align: right"]5216[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]15-Aug-12[/TD]
[TD="align: right"]2618[/TD]
[TD="align: right"]2292[/TD]
[TD="align: right"]4910[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16-Aug-12[/TD]
[TD="align: right"]281[/TD]
[TD="align: right"]666[/TD]
[TD="align: right"]947[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]16-Aug-12[/TD]
[TD="align: right"]4216[/TD]
[TD="align: right"]3441[/TD]
[TD="align: right"]7657[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #E6B8B7, align: right"]17-Aug-12[/TD]
[TD="align: right"]1645[/TD]
[TD="align: right"]3882[/TD]
[TD="align: right"]5527[/TD]
[TD="bgcolor: #E6B8B7, align: right"]33422[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E6B8B7, align: right"]17-Aug-12[/TD]
[TD="align: right"]989[/TD]
[TD="align: right"]134[/TD]
[TD="align: right"]1123[/TD]
[TD="bgcolor: #E6B8B7, align: right"]27260[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18-Aug-12[/TD]
[TD="align: right"]572[/TD]
[TD="align: right"]3740[/TD]
[TD="align: right"]4312[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18-Aug-12[/TD]
[TD="align: right"]252[/TD]
[TD="align: right"]1853[/TD]
[TD="align: right"]2105[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19-Aug-12[/TD]
[TD="align: right"]1715[/TD]
[TD="align: right"]2465[/TD]
[TD="align: right"]4180[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19-Aug-12[/TD]
[TD="align: right"]2349[/TD]
[TD="align: right"]2403[/TD]
[TD="align: right"]4752[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]20-Aug-12[/TD]
[TD="align: right"]2953[/TD]
[TD="align: right"]2155[/TD]
[TD="align: right"]5108[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20-Aug-12[/TD]
[TD="align: right"]324[/TD]
[TD="align: right"]1438[/TD]
[TD="align: right"]1762[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]21-Aug-12[/TD]
[TD="align: right"]4064[/TD]
[TD="align: right"]1301[/TD]
[TD="align: right"]5365[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]21-Aug-12[/TD]
[TD="align: right"]985[/TD]
[TD="align: right"]747[/TD]
[TD="align: right"]1732[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]22-Aug-12[/TD]
[TD="align: right"]4672[/TD]
[TD="align: right"]1823[/TD]
[TD="align: right"]6495[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]22-Aug-12[/TD]
[TD="align: right"]4112[/TD]
[TD="align: right"]4584[/TD]
[TD="align: right"]8696[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]23-Aug-12[/TD]
[TD="align: right"]3290[/TD]
[TD="align: right"]811[/TD]
[TD="align: right"]4101[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]23-Aug-12[/TD]
[TD="align: right"]3078[/TD]
[TD="align: right"]4345[/TD]
[TD="align: right"]7423[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #E6B8B7, align: right"]24-Aug-12[/TD]
[TD="align: right"]4413[/TD]
[TD="align: right"]562[/TD]
[TD="align: right"]4975[/TD]
[TD="bgcolor: #E6B8B7, align: right"]34536[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E6B8B7, align: right"]24-Aug-12[/TD]
[TD="align: right"]4441[/TD]
[TD="align: right"]736[/TD]
[TD="align: right"]5177[/TD]
[TD="bgcolor: #E6B8B7, align: right"]31647[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]25-Aug-12[/TD]
[TD="align: right"]693[/TD]
[TD="align: right"]1025[/TD]
[TD="align: right"]1718[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25-Aug-12[/TD]
[TD="align: right"]1967[/TD]
[TD="align: right"]2151[/TD]
[TD="align: right"]4118[/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]26-Aug-12[/TD]
[TD="align: right"]3280[/TD]
[TD="align: right"]2403[/TD]
[TD="align: right"]5683[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]26-Aug-12[/TD]
[TD="align: right"]1861[/TD]
[TD="align: right"]3362[/TD]
[TD="align: right"]5223[/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]27-Aug-12[/TD]
[TD="align: right"]4240[/TD]
[TD="align: right"]3749[/TD]
[TD="align: right"]7989[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]27-Aug-12[/TD]
[TD="align: right"]3531[/TD]
[TD="align: right"]4971[/TD]
[TD="align: right"]8502[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]28-Aug-12[/TD]
[TD="align: right"]3805[/TD]
[TD="align: right"]2588[/TD]
[TD="align: right"]6393[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]28-Aug-12[/TD]
[TD="align: right"]4251[/TD]
[TD="align: right"]206[/TD]
[TD="align: right"]4457[/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]29-Aug-12[/TD]
[TD="align: right"]4112[/TD]
[TD="align: right"]461[/TD]
[TD="align: right"]4573[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]29-Aug-12[/TD]
[TD="align: right"]3052[/TD]
[TD="align: right"]726[/TD]
[TD="align: right"]3778[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]30-Aug-12[/TD]
[TD="align: right"]4586[/TD]
[TD="align: right"]3324[/TD]
[TD="align: right"]7910[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30-Aug-12[/TD]
[TD="align: right"]4064[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]4129[/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="bgcolor: #E6B8B7, align: right"]31-Aug-12[/TD]
[TD="align: right"]3758[/TD]
[TD="align: right"]3380[/TD]
[TD="align: right"]7138[/TD]
[TD="bgcolor: #E6B8B7, align: right"]41404[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E6B8B7, align: right"]31-Aug-12[/TD]
[TD="align: right"]1819[/TD]
[TD="align: right"]1883[/TD]
[TD="align: right"]3702[/TD]
[TD="bgcolor: #E6B8B7, align: right"]33909[/TD]

</tbody>
Sheet1​
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
See if this helps. Run the Macro called "Main Code"

Code: 
Sub Weekly(Col As Integer)

Dim x As Long
Dim n As Integer
Dim lrow As Long


lrow = Cells(Rows.Count, Col).End(xlUp).Row


'first determine the first occurrence of Friday
For x = 2 To 8
    If Weekday(Cells(x, Col).Value) = 6 Then
        n = x
        Cells(x, Col).Offset(0, 4).FormulaR1C1 = "=SUM(R[-" & n - 2 & "]C[-1]:RC[-1])"
    End If
Next x


'then the rest of the recurrences
For x = n + 1 To lrow
   If Weekday(Cells(x, Col).Value) = 6 Then
   Cells(x, Col).Offset(0, 4).FormulaR1C1 = "=SUM(R[-6]C[-1]:RC[-1])"
    End If
Next x


End Sub


Sub MainCode()


Call Weekly(1)  'imput the number of the column the date is on
Call Weekly(7)


End Sub
 
Upvote 0
with all the rush I forgot you wanted them with a color too :rofl: Sorry about that

Code: 
Sub MainCode()


Application.ScreenUpdating = False
    Call Weekly(1)  'imput the number of the column the date is on
    Call Weekly(7)
Application.ScreenUpdating = True
End Sub


Sub Weekly(Col As Integer)


Dim x As Long
Dim n As Integer
Dim lrow As Long




lrow = Cells(Rows.Count, Col).End(xlUp).Row


'first determine the first occurrence of Friday
For x = 2 To 8
    If Weekday(Cells(x, Col).Value) = 6 Then
        n = x
        Cells(x, Col).Offset(0, 4).FormulaR1C1 = "=SUM(R[-" & n - 2 & "]C[-1]:RC[-1])"
        Cells(x, Col).Offset(0, 4).Value = Cells(x, Col).Offset(0, 4).Value
        
        'color for the date
        With Cells(x, Col).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599993896298105
        End With
        
        'color for the value
        With Cells(x, Col).Offset(0, 4).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599993896298105
        End With
    End If
Next x




'then the rest of the recurrences
For x = n + 1 To lrow
    If Weekday(Cells(x, Col).Value) = 6 Then
        Cells(x, Col).Offset(0, 4).FormulaR1C1 = "=SUM(R[-6]C[-1]:RC[-1])"
        Cells(x, Col).Offset(0, 4).Value = Cells(x, Col).Offset(0, 4).Value
        
        'color for the date
        With Cells(x, Col).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599993896298105
        End With
        
        'color for the value
        With Cells(x, Col).Offset(0, 4).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599993896298105
        End With
    End If
Next x


End Sub
 
Upvote 0
Thank you very much fredlo2008. It's working fine.

However, I would like to you make one change. The formulas are not there in weekly total columns after I run the code. I mean the values in weekly totals tuns to values instead of formulas.

I want it to be remain as formulas only like "=sum(d2:d4)" in E4 so that in the future if I change any values in qty columns, it can automatically adjust instead of re-running the macro.
 
Upvote 0
If you don't want to re-run the macro when values change, do you really even need a macro to start with?
Is this any use?

1. Formula in E2 is copied down.

2. Formulas in E2:E32 then copied and pasted to K2, Q2, W2 and AC2

3. This step assumes the dates in columns A, G, M, .. etc are the same, as in your example.
Select A2:A32 and E2:E32 and G2:G32 and K2:K32 and ... to the end of your data

4. Home ribbon tab|Conditional Formatting|New Rule...|Use a formula to determine which cells to format|Format values where this formula is true: =WEEKDAY($A2)=6 |Format...|Fill tab|Choose your colour|OK|OK

Excel Workbook
ABCDEFGHIJK
1DateQty1Qty2TotalWeekly TotalDateQty1Qty2TotalWeekly Total
21-Aug-12455928937452 1-Aug-12272129835704
32-Aug-121161244636072-Aug-1234791803659
43-Aug-1212808822162132213-Aug-1243691582595115314
54-Aug-124096192960254-Aug-12467938658544
65-Aug-12333751838555-Aug-12427035727842
76-Aug-122061336654276-Aug-124146614207
87-Aug-123757375975167-Aug-1221220362248
98-Aug-123629387675058-Aug-12314546197764
109-Aug-123426465780839-Aug-1249664215387
1110-Aug-124293233666294504010-Aug-1218263309513541127
1211-Aug-125601794235411-Aug-1219928413040
1312-Aug-1243141861617512-Aug-1263033874017
1413-Aug-1220723086515813-Aug-1243812234604
1514-Aug-1242853760804514-Aug-1220417051909
1615-Aug-122204996521615-Aug-12261822924910
1716-Aug-1228166694716-Aug-12421634417657
1817-Aug-121645388255273342217-Aug-12989134112327260
1918-Aug-125723740431218-Aug-1225218532105
2019-Aug-1217152465418019-Aug-12234924034752
2120-Aug-1229532155510820-Aug-1232414381762
2221-Aug-1240641301536521-Aug-129857471732
2322-Aug-1246721823649522-Aug-12411245848696
2423-Aug-123290811410123-Aug-12307843457423
2524-Aug-12441356249753453624-Aug-124441736517731647
2625-Aug-126931025171825-Aug-12196721514118
2726-Aug-1232802403568326-Aug-12186133625223
2827-Aug-1242403749798927-Aug-12353149718502
2928-Aug-1238052588639328-Aug-1242512064457
3029-Aug-124112461457329-Aug-1230527263778
3130-Aug-1245863324791030-Aug-124064654129
3231-Aug-123758338071384140431-Aug-1218191883370233909
33
Weekly Totals
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E21. / Formula is =WEEKDAY($A2)=6Abc
 
Upvote 0
This is the updated code to leave the formulas and not the values

Code: 
Sub MainCode()




Application.ScreenUpdating = False
    Call Weekly(1)  'imput the number of the column the date is on
    Call Weekly(7)
Application.ScreenUpdating = True
End Sub




Sub Weekly(Col As Integer)




Dim x As Long
Dim n As Integer
Dim lrow As Long








lrow = Cells(Rows.Count, Col).End(xlUp).Row




'first determine the first occurrence of Friday
For x = 2 To 8
    If Weekday(Cells(x, Col).Value) = 6 Then
        n = x
        Cells(x, Col).Offset(0, 4).FormulaR1C1 = "=SUM(R[-" & n - 2 & "]C[-1]:RC[-1])"
        'Cells(x, Col).Offset(0, 4).Value = Cells(x, Col).Offset(0, 4).Value
        
        'color for the date
        With Cells(x, Col).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599993896298105
        End With
        
        'color for the value
        With Cells(x, Col).Offset(0, 4).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599993896298105
        End With
    End If
Next x








'then the rest of the recurrences
For x = n + 1 To lrow
    If Weekday(Cells(x, Col).Value) = 6 Then
        Cells(x, Col).Offset(0, 4).FormulaR1C1 = "=SUM(R[-6]C[-1]:RC[-1])"
        'Cells(x, Col).Offset(0, 4).Value = Cells(x, Col).Offset(0, 4).Value
        
        'color for the date
        With Cells(x, Col).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599993896298105
        End With
        
        'color for the value
        With Cells(x, Col).Offset(0, 4).Interior
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.599993896298105
        End With
    End If
Next x




End Sub

The formula solution is amazing you can use that one too. :)
 
Upvote 0
One advantage still of the formula method is if the dates are changed to, say, September the totals and colours will automatically move to the new Friday rows.
The code solution would need to be run again. In fact the code would need changing, or other code run as well, to remove the existing formulas and colours as well as putting in the new ones.
 
Last edited:
Upvote 0
I had no Idea this could be achieved with a formula and conditional formatting either. When it comes to conditional formatting using formulas is a beast I have yet to tame but it refuses :rofl:

Thanks for the insights
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Assign Month number 1 to n based on sequential week range
Replies
6
Views
468
amolvijay
A
J
  • Question Question
I’m trying to get a sense of trends for each day of the week and then plot it against a moving average so I can get some context.
Replies
4
Views
267
jacquiejacuinde
J
V
  • Question Question
Max value with range
Replies
2
Views
412
vmjan02
V
V
  • Solved
Week Number
Replies
2
Views
344
vmjan02
V
T
  • Solved
Ratio calculation
Replies
6
Views
509
kevin9999
K

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top