VBA code – Sumbycellscolor

by

You can try this program code. You get results of sum by color in "L1" . Color choices are in Range(M1:M3). You have to change row number number indication in the code as indicated by me. Image given after code shows the sample data. HTH

Sub SumByColorV()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim result As Long
    Dim cSum As Long
    Dim ColIndex As Integer
    Dim CellColor As Range
    Dim rRange As Range
    Set CellColor = ActiveSheet.Cells(1, 13) 'Vary row no 1 or 2 or 3 of column 13 (M) as per yr requirement
    lastRow = ActiveSheet.Cells(Rows.Count, "K").End(xlUp).Row
    Set rRange = ActiveSheet.Range(Cells(1, 11), Cells(lastRow, 11))
    ColIndex = CellColor.Interior.ColorIndex

    For Each cl In rRange
        If cl.Interior.ColorIndex = ColIndex Then
        cSum = WorksheetFunction.Sum(cl, cSum)
        End If
    Next cl
    result = cSum
    ActiveSheet.Cells(1, 12).Value = result
End Sub

Sample data with result

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. Why MS Excel crashes and closes during Worksheet_Change Sub procedure?
  3. How to copy a line in excel using a specific word and pasting to another excel sheet?
  4. How can I send an HTTP POST request to a server from Excel using VBA?
  5. Formatting MM/DD/YYYY dates in textbox in VBA
  6. Delete row based on partial text
  7. Transpose multiple columns to multiple rows with VBA
  8. ExecuteExcel4Macro to get value from closed workbook
  9. How to put a tooltip on a user-defined function
  10. If WorkSheet(“wsName”) Exists [duplicate]
  11. VBA Shell function in Office 2011 for Mac
  12. How to copy sheets to another workbook using vba?
  13. Autofilter with column formatted as date
  14. Check if value exists in column in VBA
  15. hiding formulas in formula bar
  16. How to delete multiple rows without a loop in Excel VBA
  17. VBA Many buttons point to the same _Click sub
  18. Excel VBA Filter Change event handler
  19. How to suppress Update Links warning?
  20. How do we use restful APIs from Excel macros (vba)?
  21. Cannot VBA write data to cells in Excel 2007/2010 within a function
  22. VBA paste as values – how to
  23. How to send email to multiple recipients with addresses stored in Excel?
  24. What are the benefits of using Classes in VBA? [closed]
  25. Writing a VLOOKUP function in vba
  26. Handle cancellation of InputBox to select range
  27. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  28. VBA: How to combine two ranges on different sheets into one, to loop through
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment