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

 

[James006]

Hi Pettsy,

You could test the following :

Sub SumCountByConditionalFormat()
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Dim sumRes
Dim cntCells As Long
Dim indCurCell As Long
Dim rng As Range
cntRes = 0
sumRes = 0
' Set your own Range
Set rng = Range("H17:AU17")
cntCells = rng.CountLarge
' Set your own reference celll
indRefColor = Range("D15").Interior.Color

  For indCurCell = 1 To (cntCells - 1)
    If indRefColor = Selection(indCurCell).Interior.Color Then
      cntRes = cntRes + 1
      sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
    End If
  Next
' Set your own Output cell
Range("AW17").Value = "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _
"Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & Hex(indRefColor)

End Sub

HTH

 

[Pettsy100]

HTH, thanks tested the change code, and it didn't return the Count only the colour hex value of the cell D15.

The excel sheet has a row of dates every 3rd Column that turn red through conditional formatting when they exceed a pre-determined time frame. I need to count how many dates have turned red. I cant use the formulae that is used to trigger the conditional formatting.

Any other ideas .. thanks

 

[James006]

Most probably ... you do have a set of specific constraints ... since your macro does function properly ...

 

[Pettsy100]

Hi James006,
Not sure what you mean by specific constraints.. The original code that I posted works as needed, except instead of physically selecting the range I want to hard code it into the VBA, and hard code a cell that has the background colour, again rather than selecting it using the CNTRL key... just out of interest how would we make the -- indRefColor = Range("D15").Interior.Color , not a range but an active cell, as It looks like this is what TRL button press does on the original .. ie select the range, keep the range selected and have an active cell to pick up the background....

 

[James006]

Hi James006,
Not sure what you mean by specific constraints.. The original code that I posted works as needed, except instead of physically selecting the range I want to hard code it into the VBA, and hard code a cell that has the background colour, again rather than selecting it using the CNTRL key... just out of interest how would we make the -- indRefColor = Range("D15").Interior.Color , not a range but an active cell, as It looks like this is what TRL button press does on the original .. ie select the range, keep the range selected and have an active cell to pick up the background....

As stated in the macro ...

' Set your own reference celll
indRefColor = Range("D15").Interior.Color
' or if you do need the current cell
indRefColor = ActiveCell.Interior.Color

HTH

 

[Pettsy100]

HTH, thanks , forgot about that ... tried the modified code but left the indRefColor = ActiveCell.Interior.Color with no change. I have just stepped the code and it looks like its not actually selecting the Range("H17:AU17")

 

[Pettsy100]

If it helps the sheet has the following

Column G Column H Column I Column J Column K Column L
Row 17 Date of action Due date of action Planned date Date of action 2 Due date of action 2 Planned date 2

The due date of action is conditionally formatted to go Amber if within 60 days of the due date of the action & Red if due date is exceed. if there is no due date , cell is formatted Black, This format then continues all the way to Column AU.

Any help that you can provide would be great

 

[Pettsy100]

Just to clarify
Column G has date of action
Column H has due date of action
Column I has planned date of action etc this sequence then repeats to Column AU
hope this helps

 

[James006]

Hello again,

Wondering if a UDF would not make your life easier ...

Take a look at Counting conditionally formatted cells (vba) | Get Digital Help - Microsoft Excel resource

HTH