VBA macro highlight unique value until empty cell is reached

[svenfox]

Hello everybody,
glad to be part of this fantastic forum.

as I said above, I would like to find a macro that could highlight
a unique value until empty cell is reached and then to begin again
till the end of the range. I tried conditional formatting
but it doesn't help since it count all the range.
I hope my question is clear because I'm newbie in excel.
thanks in advance

Sven

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub HighlightUniquesPerArea()
  Dim Ar As Range, Cell As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    For Each Cell In Ar
      If Application.CountIf(Ar, Cell) = 1 Then Cell.Font.Color = vbRed
    Next
  Next
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub HighlightUniquesPerArea()
  Dim Ar As Range, Cell As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    For Each Cell In Ar
      If Application.CountIf(Ar, Cell) = 1 Then Cell.Font.Color = vbRed
    Next
  Next
End Sub[/td]
[/tr]
[/table]

If you want to go with adding Conditional Formatting like Fluff suggested instead of a direct macro like above, this code should do that...

[table="width: 500"]
[tr]
	[td]Sub HighlightUniquesPerArea()
  Dim Ar As Range, Cell As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Ar.FormatConditions.Add(xlUniqueValues).Font.Color = vbRed
  Next
End Sub[/td]
[/tr]
[/table]

 

[Fluff]

What was the error message?
Do you have any merged cells?
Also is your worksheet protected?

 

[svenfox]

the rick's macro is working but when there is only one cell it's also highlithed en that not what it's needed
I explain, my idea is if there is range of sames values and you have one or two intruders so you can detect them
but if you have two intruders of same value then the macro is not going to detect it.
hier a sample
20
20
20
20
20
30
20
20
30

as you see here we have two intruders and that is 30
I have idea. conditiontial formattaing when dupplicate but must work until blank cell is reached
en then begins again.

thank you anyway fluff and Rick I very appreciate your help.

 

[svenfox]

I have this macro (it's macro from addin PLEX) it's working but I need to make a loop from
so he stops when an empty space is reached

Sub Duplicates_Coloring()

Dim rngData As Range, i As Integer
Dim Dupes()

Colors = Array(33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 50, 53, 19, 20, 22, 27, 28)

If SelectionCheck(Selection) = False Then
MsgBox "Select range with the data where you want to highlight duplicates. This operation can not be performed on a protected sheet, with cells in the summary table, with a diagram or a picture!", vbCritical + vbOKOnly, "Incorrect Selection"
Exit Sub
End If



If Selection.CountLarge = ActiveSheet.Cells.CountLarge Then
MsgBox "Do not select the entire sheet. Select only range with the data where you want to highlight duplicates.", vbExclamation + vbOKOnly, "Incorrect Selection"
Exit Sub
End If


Set rngData = Intersect(Selection, ActiveSheet.UsedRange)
If rngData Is Nothing Then
MsgBox "Select range with the data where you want to highlight duplicates!", vbExclamation + vbOKOnly, "Incorrect Selection"
Exit Sub
End If


ReDim Dupes(1 To rngData.Cells.Count, 1 To 2)

Application.ScreenUpdating = False
rngData.Interior.ColorIndex = -4142
i = 1
For Each cell In rngData
If WorksheetFunction.CountIf(rngData, cell.Value) > 1 Then
For k = LBound(Dupes) To UBound(Dupes)
If Dupes(k, 1) = cell Then cell.Interior.ColorIndex = Dupes(k, 2)
Next k

If cell.Interior.ColorIndex = -4142 And i < UBound(Colors) Then
cell.Interior.ColorIndex = Colors(i)
Dupes(i, 1) = cell.Value
Dupes(i, 2) = Colors(i)
i = i + 1
End If
End If
Next cell

Application.ScreenUpdating = True
If i > 20 Then MsgBox i & " duplicates were found. First 20 were highlighted by color.", vbExclamation + vbOKOnly, "Too many duplicates!"
End Sub

 

[Fluff]

As this is a completely different question, can you please start a new thread.
Cheers

 

[Rick Rothstein]

According to this quote from your original message...

as I said above, I would like to find a macro that could highlight
a unique value until empty cell is reached and then to begin again
till the end of the range.

the rick's macro is working but when there is only one cell it's also highlithed

Given that a value in a single cell preceded by and followed by blank cells is inherently unique, it would seem that it should be highlighted.

...but if you have two intruders of same value then the macro is not going to detect it.

I don't see why it should detect them as they are not unique values.

 

[svenfox]

Sorry I'll start a new threat thanks so much Fluff and Rich

 

[Rick Rothstein]

Sorry I'll start a new threat thanks so much Fluff and Rich

Your response in Message #14 would seem to indicate that neither of my macros worked exactly as you wanted. In Message #17 I told you why I coded them as I did. I was kind of expecting you to then come back explaining in what way my codes failed to do what you actually wanted. Is your above response suggesting you are okay with the code(s) I provided?

 

[svenfox]

No rick you're macro has worked. thank you so much