VBA Counting Conditionally formatting cells

[Pettsy100]

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

1. Select a range or ranges where you want to count colored cells or/and sum by color if you have numerical data.
2. Press and hold Ctrl, select one cell with the needed color, and then release the Ctrl key.
3. Press Alt+F8 to open the list of macros in your workbook.
4. 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

 

[Pettsy100]

Hi,
I tried using a number of the UDF that I found on-line before posting on the site, but unfortunately I couldn't get them to work , which I think is because most don't work with Excel 2013 and guess what I have got.
If anyone knows of a UDF that works with Excel 2013 please let me know.
back to the VBA code .. any ideas why it wouldn't be selecting the range in the code?

 

[James006]

Hi again,

Would you be willing to share your workbook ...?

 

[Pettsy100]

hi, sorry for the delay but been out of office.... happy to share the work book

 

[Pettsy100]

Hi james006, As initially indicated , new to the Forum, how can I share the workbook with you?