# Thread: Excel formula based on color?

1. ## 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. 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

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

Split2874 (2014-12-05)

4. 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. 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

6. 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. 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

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

Split2874 (2014-12-05)

9. thanks for your help. I was able to get it trusted. I will check back with you Monday if I need anymore help

10. 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
End With
With chColor
.Caption = "ChangeColor"
.Style = msoButtonCaption
.OnAction = "ChangeColor"
End With
On Error GoTo 0
End Sub

11. 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. 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. Split,

That what my original post did.

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

16. 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
•