Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Excel formula based on color?

    I am trying to color code some items on a spreadsheet.

    I want them separated by color. I want to make calculation based on the color of the cell. So when I change the color of the cell, it will calculate with other cells of the same color.

    I have tried a few If statements that I have found but I cannot get it to work.

    AR Summary - QB.xlsx

    Thanks for any help with this

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Split,

    Welcome to the Lounge as a new poster!

    Here's a User Defined Function (UDF) that will do what I think you want. It is called by placing a formula in Column C next to the colored total indicator. It will pickup the color form the titled cell in column A and then sum all the numbers in column M with that color.
    Code:
    Option Explicit
    
    Public Function SumColoredCells(Target As Range) As Double
    
      Dim lCntr         As Long
      Dim lSumColor     As Long
      Dim lRows         As Long
      Dim rngBase       As Range
      
      lSumColor = Target.Offset(0, -1).Interior.ColorIndex
      SumColoredCells = 0
      Set rngBase = Range("M2")
      lRows = Range("M2:M45").Count - 1 'Note: A named range could be used here!
      
      For lCntr = 0 To lRows
         If rngBase.Offset(lCntr, 0).Interior.ColorIndex = lSumColor Then
           SumColoredCells = SumColoredCells + rngBase.Offset(lCntr, 0).Value
         End If
      Next lCntr
    
    End Function   'SumColoredCells
    Results:
    split.JPG

    Test File: Split-AR Summary - QB.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Split2874 (2014-12-05)

  4. #3
    New Lounger
    Join Date
    Nov 2014
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks for the help.

    I tried looking at the test, but when I enable editing the totals for =SumColoredCells(C48) change to Value#

    What do I do with that code?

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Split,

    You have to copy the file into a "Trusted Location" as defined in your File -> Options -> Trust Center -> Trust Center Settings... ->Trusted Locations menu. While in there don't forget to check the Macro Settings and make sure they are set to "Enable all macros". HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    New Lounger
    Join Date
    Nov 2014
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I am new to this part of excel. What file do I copy? I clicked on the test and followed the steps you laid out, but when I got to trusted locations menu, I am not sure what to do.

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Split,

    Here are some instructions.

    Save the file I posted to a folder on your hard drive. Then follow the instructions above to add that location to the Trusted Locations. Don't forget to set the Macro Security level! HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Split2874 (2014-12-05)

  9. #7
    New Lounger
    Join Date
    Nov 2014
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    thanks for your help. I was able to get it trusted. I will check back with you Monday if I need anymore help

  10. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Split,

    I interpreted your request a little differently than RG. Here is some code that does the following:

    1. Right click a cell in the Totals column (M2 to M45) then Select ChangeColor in the Context menu. (set to work only in that range)
    2. In the Color dialog box that opens, pick the color you want to change the cell to then click OK
    3. The cell changes to that color and all of the cells in the Total column (M2 to M45) with a matching color will be summed in M46
    4. Selecting a new color (one that does not match another cell) will total only that cell.
    5. Selecting a cell with an existing color and changing to none will remove the color and all cells with no color will be summed.

    See Images

    HTH,
    Maud

    Right click cell and select ChangeColor:
    Color1.png

    Pick a color then click OK:
    Color2.png

    Note the cell color change and the summed value in M46:
    Color3.png

    Place in a standard module:
    Code:
    Public Sub ChangeColor()
    '---------------------------------
    'DECLARE AND SET VARIABLES
    Dim cell As Range
    Dim rng As Range
    Set rng = Range("M2:M45")
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    SumCells = 0
    '---------------------------------
    'DETERMINE IF SELECTED CELL IN TOTALS COLUMN
    If Not Intersect(ActiveCell, rng) Is Nothing Then
    '---------------------------------
    'ASSIGN COLOR TO ACTIVE CELL
        Application.Dialogs(xlDialogPatterns).Show
            Activecolor = ActiveCell.Interior.Color
        For Each cell In rng
            If cell.Interior.Color = Activecolor Then
                SumCells = SumCells + cell
            End If
        Next cell
        End If
    '---------------------------------
    'RESET COLOR 1 BACK TO ORIGINAL COLOR
        ActiveWorkbook.Colors(1) = 1
    '---------------------------------
    'SUM TOTAL OF COLORED CELLS
        If SumCells = 0 Then Exit Sub
        [m46] = SumCells
    End Sub
    Place in the ThisWorkbook Module:
    Code:
    Private Sub Workbook_Deactivate()
        On Error Resume Next
                With Application
                    .CommandBars("Cell").Controls("ChangeColor").Delete
                End With
        On Error GoTo 0
    End Sub
    
    
    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim chColor As CommandBarButton
        On Error Resume Next
            With Application
                .CommandBars("Cell").Controls("ChangeColor").Delete
                Set chColor = .CommandBars("Cell").Controls.Add(Temporary:=True)
            End With
            With chColor
               .Caption = "ChangeColor"
               .Style = msoButtonCaption
               .OnAction = "ChangeColor"
            End With
        On Error GoTo 0
    End Sub
    Attached Files Attached Files

  11. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Add the following command as the last line in the ChangeColor routine and M46 will also change to the same color indicating which cells are being summed.

    [m46].Interior.Color = Activecolor

  12. The Following User Says Thank You to Maudibe For This Useful Post:

    Split2874 (2014-12-08)

  13. #10
    New Lounger
    Join Date
    Nov 2014
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thank you that is what I was looking for.

    One thing is it possible to have it total next to the corresponding colors?

    So when I put an item in green it will total next to collected.

  14. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Split,

    That what my original post did.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #12
    New Lounger
    Join Date
    Nov 2014
    Posts
    11
    Thanks
    4
    Thanked 0 Times in 0 Posts
    RetiredGeek,

    I only get the total of the colored cells. It does not separate by color. I might be doing something incorrectly.

    I got it to work

    Thanks!!
    Last edited by Split2874; 2014-12-08 at 13:54.

  16. #13
    New Lounger
    Join Date
    Oct 2014
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the great guide book I will sure try this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •