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

 

[alansidman]

Suggest you post a sample of before and after so that we have a better idea of what is in your mind.

 

[Fluff]

Does this do what you want.

Sub highlight()
   Dim Rng As Range
   For Each Rng In Range("B:B").SpecialCells(xlConstants).Areas
      Rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
         "=COUNTIF(" & Rng.Address & "," & Rng.Cells(1, 1).Address(0, 0) & ")=1"
      Rng.FormatConditions(1).Interior.Color = 45678
   Next Rng
End Sub

This is based on col B & assumes that your values are values rather than formulae

 

[svenfox]

Thank you Fluff, but I got error message. I'll explain it
in excel sheet
I have
20
20
30
20

20
20
30
20
as you see I have one empty space (it can be more) between the two
the macro need to detect the value (30) as unique and highlight it
in the two. by other words he must stop when there is a empty cells
and do the highlight and then begin again until end of my selected area
regards
Sven

 

[Fluff]

Do the cells contain hard values, or formulae?
Also is the blank cell actually blank, or could it have a space ?

 

[svenfox]

hard values and the blank cell is empty nothing in.

 

[Fluff]

This is what I'm getting
[TABLE="class: grid, width: 78"]
<tbody>[TR]
[TD]E07000241
[/TD]
[/TR]
[TR]
[TD]E07000242[/TD]
[/TR]
[TR]
[TD]E07000242[/TD]
[/TR]
[TR]
[TD]E07000098
[/TD]
[/TR]
[TR]
[TD]E07000099
[/TD]
[/TR]
[TR]
[TD]E07000241
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]E07000241[/TD]
[/TR]
[TR]
[TD]E07000245
[/TD]
[/TR]
[TR]
[TD]E07000241[/TD]
[/TR]
[TR]
[TD]E07000241[/TD]
[/TR]
[TR]
[TD]E07000241
[/TD]
[/TR]
[TR]
[TD]E07000241[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]E07000241[/TD]
[/TR]
[TR]
[TD]E07000241[/TD]
[/TR]
[TR]
[TD]E07000241[/TD]
[/TR]
[TR]
[TD]E07000241[/TD]
[/TR]
[TR]
[TD]E07000242
[/TD]
[/TR]
[TR]
[TD]E07000241[/TD]
[/TR]
[TR]
[TD]E07000241[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8000025[/TD]
[/TR]
[TR]
[TD="align: right"]8000025[/TD]
[/TR]
[TR]
[TD="align: right"]8000028
[/TD]
[/TR]
[TR]
[TD="align: right"]8000025
[/TD]
[/TR]
[TR]
[TD="align: right"]8000029
[/TD]
[/TR]
</tbody>[/TABLE]

Where the values in red signify which cells have formatting.
Is this what you are looking for & is your data in this sort of layout?

 

[svenfox]

yes it's what I'm looking for only I highlight all the cell

 

[Fluff]

What error did you get & what line was highlighted?

 

[svenfox]

When I run the macro . this line get yellow. I paste the data above in column B obviously.

Rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(" & Rng.Address & "," & Rng.Cells(1, 1).Address(0, 0) & ")=1"