Dear friends,
Is there any formula or VBA Code that allows me to subtotal a group of cells based on their cell background color but as enlisted in the "Desired formula" column as follows:
D4 Subtotals D5:D8
D5 Subtotals D6
D7 Subtotals D8
D6 And D8 Are data captured manually
Thanks a lot i hope this is easy to understand.
Thanks in advance.
[TABLE="class: grid, width: 790"]
<tbody>[TR]
[TD]Cell
[/TD]
[TD]C
[/TD]
[TD] D
[/TD]
[TD] E
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reference
[/TD]
[TD]Code
[/TD]
[TD] Amount
[/TD]
[TD]Formula (Using Cell Reference)
[/TD]
[TD]Description
[/TD]
[TD]Cell Color
[/TD]
[TD]Desired Formula
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD] 20.00
[/TD]
[TD]=SUBTOTAL(9,D5:D8)
[/TD]
[TD]Second Subtotal (Range: 1:1 to 1.2.1)
[/TD]
[TD]Cell Color: Yellow
[/TD]
[TD]Subtotal til the next yellow cell
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1.1
[/TD]
[TD] 10.00
[/TD]
[TD]=SUBTOTAL(9,D6)
[/TD]
[TD]First Subtotal (Range 1.1.1)
[/TD]
[TD]Cell Color: Green
[/TD]
[TD]Subtotal til the next green cell
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1.1.1
[/TD]
[TD] 10.00
[/TD]
[TD]DATA INPUT
[/TD]
[TD]Data Captured Manually
[/TD]
[TD]Cell Color: No Color
[/TD]
[TD]No formula
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1.2
[/TD]
[TD] 10.00
[/TD]
[TD]=SUBTOTAL(9,D8)
[/TD]
[TD]First Subtotal (Range 1.2.1)
[/TD]
[TD]Cell Color: Green
[/TD]
[TD]Subtotal til the next green cell
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]1.2.1
[/TD]
[TD] 10.00
[/TD]
[TD]DATA INPUT
[/TD]
[TD]Data Captured Manually
[/TD]
[TD]Cell Color: No Color
[/TD]
[TD]No formula
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]2
[/TD]
[TD] 20.00
[/TD]
[TD]=SUBTOTAL(9,D10:D13)
[/TD]
[TD]Second Subtotal (Range: 2.1 to 2.2.1)
[/TD]
[TD]Cell Color: Yellow
[/TD]
[TD]Subtotal till the next yellow cell
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]2.1
[/TD]
[TD] 10.00
[/TD]
[TD]=SUBTOTAL(9,D11)
[/TD]
[TD]First Subtotal (Range 2.1.1)
[/TD]
[TD]Cell Color: Green
[/TD]
[TD]Subtotal til the next green cell
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]2.1.1
[/TD]
[TD] 10.00
[/TD]
[TD]DATA INPUT
[/TD]
[TD]Data Captured Manually
[/TD]
[TD]Cell Color: No Color
[/TD]
[TD]No formula
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]2.2
[/TD]
[TD] 10.00
[/TD]
[TD]=SUBTOTAL(9,D13)
[/TD]
[TD]First Subtotal (Range 2.2.1)
[/TD]
[TD]Cell Color: Green
[/TD]
[TD]Subtotal til the next green cell
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]2.2.1
[/TD]
[TD] 10.00
[/TD]
[TD]DATA INPUT
[/TD]
[TD]Data Captured Manually
[/TD]
[TD]Cell Color: No Color
[/TD]
[TD]No formula
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Grand Total
[/TD]
[TD] 40.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Is there any formula or VBA Code that allows me to subtotal a group of cells based on their cell background color but as enlisted in the "Desired formula" column as follows:
D4 Subtotals D5:D8
D5 Subtotals D6
D7 Subtotals D8
D6 And D8 Are data captured manually
Thanks a lot i hope this is easy to understand.
Thanks in advance.
[TABLE="class: grid, width: 790"]
<tbody>[TR]
[TD]Cell
[/TD]
[TD]C
[/TD]
[TD] D
[/TD]
[TD] E
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reference
[/TD]
[TD]Code
[/TD]
[TD] Amount
[/TD]
[TD]Formula (Using Cell Reference)
[/TD]
[TD]Description
[/TD]
[TD]Cell Color
[/TD]
[TD]Desired Formula
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD] 20.00
[/TD]
[TD]=SUBTOTAL(9,D5:D8)
[/TD]
[TD]Second Subtotal (Range: 1:1 to 1.2.1)
[/TD]
[TD]Cell Color: Yellow
[/TD]
[TD]Subtotal til the next yellow cell
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1.1
[/TD]
[TD] 10.00
[/TD]
[TD]=SUBTOTAL(9,D6)
[/TD]
[TD]First Subtotal (Range 1.1.1)
[/TD]
[TD]Cell Color: Green
[/TD]
[TD]Subtotal til the next green cell
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1.1.1
[/TD]
[TD] 10.00
[/TD]
[TD]DATA INPUT
[/TD]
[TD]Data Captured Manually
[/TD]
[TD]Cell Color: No Color
[/TD]
[TD]No formula
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1.2
[/TD]
[TD] 10.00
[/TD]
[TD]=SUBTOTAL(9,D8)
[/TD]
[TD]First Subtotal (Range 1.2.1)
[/TD]
[TD]Cell Color: Green
[/TD]
[TD]Subtotal til the next green cell
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]1.2.1
[/TD]
[TD] 10.00
[/TD]
[TD]DATA INPUT
[/TD]
[TD]Data Captured Manually
[/TD]
[TD]Cell Color: No Color
[/TD]
[TD]No formula
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]2
[/TD]
[TD] 20.00
[/TD]
[TD]=SUBTOTAL(9,D10:D13)
[/TD]
[TD]Second Subtotal (Range: 2.1 to 2.2.1)
[/TD]
[TD]Cell Color: Yellow
[/TD]
[TD]Subtotal till the next yellow cell
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]2.1
[/TD]
[TD] 10.00
[/TD]
[TD]=SUBTOTAL(9,D11)
[/TD]
[TD]First Subtotal (Range 2.1.1)
[/TD]
[TD]Cell Color: Green
[/TD]
[TD]Subtotal til the next green cell
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]2.1.1
[/TD]
[TD] 10.00
[/TD]
[TD]DATA INPUT
[/TD]
[TD]Data Captured Manually
[/TD]
[TD]Cell Color: No Color
[/TD]
[TD]No formula
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]2.2
[/TD]
[TD] 10.00
[/TD]
[TD]=SUBTOTAL(9,D13)
[/TD]
[TD]First Subtotal (Range 2.2.1)
[/TD]
[TD]Cell Color: Green
[/TD]
[TD]Subtotal til the next green cell
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]2.2.1
[/TD]
[TD] 10.00
[/TD]
[TD]DATA INPUT
[/TD]
[TD]Data Captured Manually
[/TD]
[TD]Cell Color: No Color
[/TD]
[TD]No formula
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Grand Total
[/TD]
[TD] 40.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]