Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

sum conditional format cells


How do i sum conditional format cells?



Hi Buckman and welcome to the Forum

Your question is incomplete so difficult to answer. Are you talking about conditional formatting that sets the cell colour, the font colour/typeface, borders or cell number formatting- what?

Please EDIT your original question to explain what you're looking for and, if possible,  upload a representative Excel file using the Add Files... button
John_Ru (rep: 2867) Dec 3, '21 at 4:14 am
Add to Discussion



Hi Buckman and welcome to the Forum

Your question is incomplete so difficult to answer with certainty. Conditional formatting can set the cell colour, the font colour/typeface, borders, cell number formatting or a combination of those.

Probably the most common use of CF is to set the cell fill colour (so the example below shows how to sum cells like that). It needs VBA and use of the Range.DisplayFormat property (that's the Microsoft guidance on same)since CF colouring sits "above" the normal cell colouring (pale yellow in A1:H10 of the example file).

The attached example workbook has two conditional formatting rules set in A1:H10. One sets the colour fill to red if the value is >3 and the other sets the row to green if the value in column A is 10 or more (that overrides the first rule). There's a pale yellow button saying "Pick and sum a range based on CF colour" - click that and you can pick a range to sum and a (single) cell with the conditional formatting you want to sum. You get the result SumCond in a verbose VBA message box but you could copy it to a cell or do other things with it.

Sub SumCondByColour()

Dim Rng As Range, Cl As Range, ColCl As Range
Dim ColToSum As Long, SumCond As Double

' get range from user
Set Rng = Application.InputBox("Range to sum", "Choose...", Type:=8)

' get user to pick only one cell
Set ColCl = Application.InputBox("A cell with the conditonal formatting colour to sum", "Choose...", Type:=8)
If ColCl.Count <> 1 Then
    MsgBox "Please retry, picking only one cell"
    Exit Sub
End If

'get the cell colour
CondToSum = ColCl.DisplayFormat.Interior.Color

'loop though cells range and add numbers if same cond colour
For Each Cl In Rng
    If IsNumeric(Cl.Value) And Cl.DisplayFormat.Interior.Color = CondToSum Then
        SumCond = SumCond + Cl.Value
    End If
Next Cl
' report selections and sum
MsgBox "Sum of cells in " & Rng.Address & _
" with conditional formatting colour like " _
& ColCl.Address & " =" & SumCond

End Sub
Note that you can choose discontinuous ranges or ranges outside A1:A10 in the example (but they won't have your CF colour cell as it stands).

Hope this is what you wanted.



Did that work for you?
John_Ru (rep: 2867) Dec 7, '21 at 7:16 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login