Sum_FromRange_AsPer_ConditionalFormatting

Anand... Thanks buddy for coming up with this Scenario... Atleast, I got a topic to post on my blog ;)

Well, There's so much that we can do with VBA but unfortunately we hardly use it in our daily work. Instead we choose the hard ways to go about it which is manual. I would urge you all to participate by coming up with Issue description wherein you need solution using VBA.
- Amit, Happy to Help! [Milind ignore the tag :)]

In this post I have used CF as an abbreviation for conditional formatting.

Issue Description: Sum up the values from the selected range as per the applied CF

Refer to below Snapshot


Solution:

Step 1: In the Excel file with applied CF, press Alt+f11 to open VBE and Insert MODULE

Step 2: Copy & Paste the below code of Macro Sum_As_Per_CF_Color in the MODULE

Sub Sum_As_Per_CF_Color()

Dim nbr_rows As Integer, i As Integer
Dim ftotal As Long, stotal As Long
Dim rcell As Range

ftotal = 0
stotal = 0
Set rcell = ActiveWindow.RangeSelection

nbr_rows = WorksheetFunction.Count(rcell)

Selection.Cells(1, 1).Select

For i = 1 To nbr_rows
If ActiveCell.Value > ActiveCell.FormatConditions.Item(1).Formula1 Then
ftotal = ftotal + ActiveCell.Value
Else
If (ActiveCell.Value >= ActiveCell.FormatConditions.Item(2).Formula1) And (ActiveCell.Value <= ActiveCell.FormatConditions.Item(2).Formula2) Then
stotal = stotal + ActiveCell.Value
End If
End If
ActiveCell.Offset(1, 0).Select
Next i

Cells(2, 3).Value = ftotal
Cells(3, 3).Value = stotal

End Sub


Step 3: Save the VBE Project and Press Alt+F11 to switch to EXCEL View

Step 4: Select the range with applied CF (In our eg: Select Range G4:G14)

[ Note: Applied CF details are shown from range A1 to A3 1st condition - checks if value is greater than 50 & 2nd condition- checks if value lies between 35 to 50 ]

Step 5: Go to Tools->Macro->Macros (Press Alt+F8)

Step 6: Select Sum_As_Per_CF_Color and click on Run.

Step 7: The total value as per the applied CF is displayed in cells C2 and C3 respectively.

and now we have a reason to smile... SMILE PLEASE !!