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]

The best I can offer is to go to Conditional formatting and select Manage rules.
Go to the top of the dialog box and select This Worksheet.
A selection of ranges appropriate each formula present will be available eg B2:B20.
Therefore you count will be 18 formatted by that formula.

Of course that can become more complicated when two ranges may overlap because of one or more formulae.
Sorry I can't do better.

 

[Rick Rothstein]

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

I don't know of a way to do this with a formula nor a UDF (User Defined Function), but I can give you a macro that I think should work...

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

 

[FDibbins]

Hi all,...My question is this... Is there a formula that will count the number of highlightes cells for me?...

Short answer - no there isn't a regular formula to do this..

However, there are other ways you could do this, using maybe COUNTIF()

If there will be different duplicates, then maybe...
=--countif($A$1:a1,a1)>1
Copied down
repeat as needed
Then just count the 1's

 

[Rick Rothstein]

If there will be different duplicates, then maybe...
=--countif($A$1:a1,a1)>1
Copied down

Copy down? The OP said the range was A1:E500.

 

[FDibbins]

Rick, yup, I got that, that's why I added - "repeat as needed"

maybe this, instead?
=--countif($A$1:E1,$a$1:E1)>1
ARRAY entered, using CTRL SHIFT ENTER, not just enter

 

[Tetra201]

Will this work?

=SUMPRODUCT(--(COUNTIF(A1:E500,A1:E500)>1))

 

[BrianJN1]

@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.

 

[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.

What version of Excel are you using?

 

[BrianJN1]

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