Sum by color in VBA with multiple conditions

Petula22

New Member
Joined
Mar 22, 2018
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello, I have been googling and searching an answer but I can't find anything. I have a sheet which highlights certain cells with conditional formatting. I need to sum these cells so I have written a macro and it works and the formula result is correct but in the worksheet it shows as #VALUE!. I have checked the cell format and everything is as it should be. Can someone please help me?

VBA Code:
Option Explicit

Function SumColor(sumRange As Range) As Double

Application.Volatile


Dim cell As Range


For Each cell In sumRange

If cell.DisplayFormat.Interior.Color = 5296274 Or cell.DisplayFormat.Interior.Color = 15773696 Or cell.DisplayFormat.Interior.Color = 49407 Or cell.DisplayFormat.Interior.Color = 12566463 Then

SumColor = SumColor + cell.Value

End If


Next cell

End Function
 

Attachments

  • 2021-07-16 14_16_38-TS 2022 DIP-AND-ANG-PIVHE - Excel.png
    2021-07-16 14_16_38-TS 2022 DIP-AND-ANG-PIVHE - Excel.png
    10.4 KB · Views: 14
  • 2021-07-16 14_17_46-TS 2022 DIP-AND-ANG-PIVHE - Excel.png
    2021-07-16 14_17_46-TS 2022 DIP-AND-ANG-PIVHE - Excel.png
    27.7 KB · Views: 16

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
You cannot use DisplayFormat in a function that is being called from a worksheet.
As the colour is being set by conditional formatting, you can use a normal formula with the same criteria as the CF rules
 
Upvote 0
You cannot use DisplayFormat in a function that is being called from a worksheet.
As the colour is being set by conditional formatting, you can use a normal formula with the same criteria as the CF rules
Thank you for the reply. Unfortunately I cannot (or at least I did not find a way) use SUMIF as normal formula or any other SUM function. Basicly the conditions are to sum each column and take only the numbers that are green, blue, yellow or grey (or if we take the conditional formatting if in column A the corresponding row contains "n", "o", "c" or "s").
 
Upvote 0
What is your conditional formatting rule?
 
Upvote 0
Is this enough information?


TS 2022 DIP-AND-ANG-PIVHE.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1newdeclinednocds400GENERAL CLOSE OUT DATES LEISURE
2offeredsigned
3confirmedTRUE
4
5JANUARYRates Wkdy/WkndMat.1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.
620192020202120222023SaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSuMo
7dAvalon PRG 2021/2022xxx3030
8oBoscolo Budapest Pragaxxx24
9oTravelPlan ADS - 5Ax0%0%282828155
10
11Total TS############################################################################################################################
Diplomat
Cell Formulas
RangeFormula
J3J3=OR($A7:$A9="n",$A7:$A9="o",$A7:$A9="c",$A7:$A9="s")
I11:AM11I11=SumColor(I7:I10)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I7:AM10Expression=AND($A7="c";I7>0)textNO
I7:AM10Expression=AND($A7="n";I7>0)textNO
B7:B10Expression=$A7="c"textNO
B7:B10Expression=$A7="n"textNO
I7:AM10Expression=AND($A7="s";I7>0)textNO
B7:B10Expression=$A7="s"textNO
I7:AM10Expression=AND($A7="o";I7>0)textNO
B7:B500Expression=$A7="o"textNO
I7:AM9Expression=AND($A7="d";I7>0)textNO
B7:B500Expression=$A7="d"textNO
Cells with Data Validation
CellAllowCriteria
A7:A10List=$D$1:$H$1
 
Upvote 0
Ok, how about
Excel Formula:
=SUM(SUMIFS(I7:I10,$A$7:$A$10,{"n","o","c","s"}))
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,451
Members
448,573
Latest member
BEDE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top