How to count the number of conditional formated cells in range

[Double99]

Hi all,

I have a range of cells which contain personal ID numbers. The range goes from column A1:E500. I hightlight the range and the select the 'conditional format' function of 'Highlight Dulipcates'. It finds and highlights 26 duplicate values across the range. I only know its 26 because I've had to scroll through and manually count the highlight cells which the conditional format has found. My question is this... Is there a formula that will count the number of highlightes cells for me?... It's annoying that I have to count by hand from a spreadsheet and I'm sure there must be away round this. Greatful as always Cris

 

[BrianJN1]

@Rick, I'm not sure, HMAker reports an item I posted as being 2013/2016, I think 2013 ( do have a subscription for 365 but I am not running that here.

 

[Tetra201]

@Tetra201, that is not taking conditional formatting into account which the OP requires.

Actually, the question was:

... Is there a formula that will count the number of highlightes cells for me?...

 

[BrianJN1]

Sorry, my mistake. I was too focused on Cond. Format and highlight and forgot about the duplication.

 

[Double99]

Thanks for all the tips, unfortunately I can't get any to work. I found VBA code that can count coloured cells, unfortunately it can't count conditional formated cells (even though its the same colour). I thought that there might be some underline code/formula that you type in that replicates the same process the conditional format is doing (ie something that is making it go TRUE or False?

 

[Rick Rothstein]

@Rick, comment #3 .

Rick I conditionally formatted a few ranges, somewhat ad hoc, in the within rows 1 to 20 across columns A to E.
When I ran your macro I was faced with a Runtime "9" error emanating from your IF line.

I just thought of why it may not have worked for you... did you set both a fill color and a font color for your test Conditional Format? The OP chose to search for duplicates... when you Conditionally Format for that, both the fill and font colors are set to something. It appears that the default font color for Conditional Formatted font is xlAutomatic (-4105), not vbBlack (0), so the second test for font color that my code performs is failing for you. If you want to count the number of Conditionally Formatted cells where only the fill color is applied conditionally, you would have to do it this way...

[table="width: 500"]
[tr]
	[td]Sub ConditionallyFormattedColorCount()
  Dim Cell As Range, DupeCount As Long, FillClr As Long, BothClr As Long
  With Range("A1:E500")
    FillClr = .Cells(1).FormatConditions(1).Interior.Color
    For Each Cell In .Cells
      If Cell.DisplayFormat.Interior.Color = FillClr And Cell.Interior.Color <> FillClr Then
        DupeCount = DupeCount + 1
      End If
    Next
  End With
  MsgBox DupeCount
End Sub[/td]
[/tr]
[/table]

NOTE: The above code assumes that the cells you have Conditionally Formatted with a fill color are never manually filled with that same color (not sure why anyone would ever want to do that, but if they did, this macro would not produce the correct count)... other cells not controlled by the Conditional Format can be manually filled with that same color if desired though (although I am not sure why anyone would want to do that either).

 

[BrianJN1]

Yes Rick, I ran with the default font colour. When I passed the mouse over the reported error, a length numeral (7 digits or so) indicated the background colour while a "0" was reported for the font.

 

[Double99]

All,

I think I have worked out an alternative way of achieving this.

My range goes from column A1:E500 and highlighted duplicates (26 were found)

In cell G1, I have typed in =A1 and have taken the range to G1:K500, therefore replicating all the cell values in A1:E500

In Cell N1, I have typed in =COUNTIF($A$1:$E$500,G1) this counts all the unique values of cell G1 within range of A1:E500

I have then dragged cell N1 across the range of N1:R500 If the value is unique the cell will have a value of 1 it its a duplicate it will be 2 or higher.

All that remains is for me to do is to identify an empty cell and type in =COUNTIF(N1:R500,">1")/2 and this formula calculates the number of duplicate cells. (I have dived it by 2, as this will show the number of dulpicate values). It displays the result of 13 duplicates.

Although a bit long winded, it achieves the final result of headline counting the number of duplicate values in a range, which is complemented by the conditional format that highlights the dulplicate values that need investigating.

I'm feeling quite pleased with myself, thank you for all your help!!!, I wish there was an easier way, but I think it works and that the main thing!

 

[Rick Rothstein]

Thanks for all the tips, unfortunately I can't get any to work.

In what way did the code I posted in Message #3 not work for you?