1st timer on the message board so forgive any errors.
The code below works really well but I am trying to modify it so that I can a) define the range in the code ( h17:AU17), define a specific cell which has the colour (D15)
and output the count of cells to AW17.
Hoping someone can help me as I am struggling
Sub SumCountByConditionalFormat()
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Dim sumRes
Dim cntCells As Long
Dim indCurCell As Long
cntRes = 0
sumRes = 0
cntCells = Selection.CountLarge
indRefColor = ActiveCell.DisplayFormat.Interior.Color
For indCurCell = 1 To (cntCells - 1)
If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
cntRes = cntRes + 1
sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
End If
Next
MsgBox "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _
"Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & _
Hex(indRefColor) & vbCrLf, , "Count & Sum by Conditional Format color"
End Sub
The code below works really well but I am trying to modify it so that I can a) define the range in the code ( h17:AU17), define a specific cell which has the colour (D15)
and output the count of cells to AW17.
Hoping someone can help me as I am struggling
- Select a range or ranges where you want to count colored cells or/and sum by color if you have numerical data.
- Press and hold Ctrl, select one cell with the needed color, and then release the Ctrl key.
- Press Alt+F8 to open the list of macros in your workbook.
- Select the SumCountByConditionalFormat macro and click Run
Sub SumCountByConditionalFormat()
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Dim sumRes
Dim cntCells As Long
Dim indCurCell As Long
cntRes = 0
sumRes = 0
cntCells = Selection.CountLarge
indRefColor = ActiveCell.DisplayFormat.Interior.Color
For indCurCell = 1 To (cntCells - 1)
If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
cntRes = cntRes + 1
sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
End If
Next
MsgBox "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _
"Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & _
Hex(indRefColor) & vbCrLf, , "Count & Sum by Conditional Format color"
End Sub