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 !!

Sum Total as Per Cell Color

Hello All !
I Logged in after a long time.... ! Hope you all are doing good and roaring in Excel as well.
Joy... from JLT... This solution is dedicated to you. After a long time, I came across someone (Joy) who was trying to ensure that he gets all his excel issues re-solved in the short stipulated time that we got to discuss on excel. Jokes apart, Joy as I promised here is a solution that can be used for adding cell values with a particular color. Though, this will not work for cell color used by conditional formatting.

Issue Description: Sum Total the cell values as per the given cell color. In this example, as per fill color of cell C2.

Refer to the SnapShot below


Solution: Developed a Macro named Sum_As_Per_Color()

Step 1: In Your Excel File, Go To Tools->Macro->Visual Basic Editor (Shortcut: Alt+F11)
Step 2: Go To Insert->Module
Step 3: Within the inserted Module, Copy Paste the below code

Sub Sum_As_Per_Color()

Dim nbr_rows As Integer, i As Integer, val As Integer, col_nbr As Integer, row_pos As Integer
Dim total As Long

total = 0 'initialise total value to 0

'Take inputs from the User

'Nbr of rows
nbr_rows = InputBox("Enter the nbr of rows")

'Col_nbr
col_nbr = InputBox("Enter the column Index Nbr with Data")

'Row Nbr where Range Starts
row_pos = InputBox("Starting Adding from what Row Nbr?")

'Fill color of Cell C2
val = Cells(2, 3).Interior.ColorIndex

For i = 1 To nbr_rows
If Cells(row_pos, col_nbr).Interior.ColorIndex = val Then
total = total + ActiveCell.Value
End If
row_pos = row_pos + 1
ActiveCell.Offset(1, 0).Select
Next i

ActiveCell.Value = total 'Print the Final sum total in the cell below the RANGE

End Sub

Step 4: Save the File & close Visual Basic Editor
Step 5: Press Alt+F8, Run the Macro with the name Sum_As_Per_Color() and verify the result.

Note: Remember that before running the Macro, select the 1st cell from the range ie. Make it active. Also, when you run the Macro, for this example hereby is a guideline for input
1. For total nbr of rows=> 12
2. Col Index would be => 5
3. Range starts at Row Nbr=> 4

Joy & Team, Remember to ask me for lunch tommorow :).............CHEERS!!