On Apr 6, 8:50 pm, "Don Guillett" wrote:
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Stefano" wrote in message

...

On Apr 5, 4:00 pm, JLatham wrote:
Sorry, I just coded what you wrote ... "I'm really driving mad to find
out a
way to highlight any row in my worksheet that has 3 same value."

At the time you didn't specify within any selection or range of rows, nor
any other requirement, so it does as requested, other than using a total
of 3
or more rather than just 3 exactly.

Hopefully, one of the other offers of help hits the mark closer.

"Stefano" wrote:
On Apr 4, 7:45 pm, JLatham wrote:
EXACTLY 3 of the same value? Or 3 or more? What columns are
involved? Is
there a column that would always have an entry in it for any row with
any
data in it? If so, which column would that be?

I've assumed column A will always have an entry on any row with any
entries
in it, and that you want to cells shaded yellow, and that you want to
do all
of this when the match count is 3 or more.

To check out the code:
Make a copy of your workbook. With the copy open, press [Alt]+[F11]
to open
the VB Editor and then choose Insert--Module and copy the code below
and
paste it into the module presented to you. Make any edits needed to
the
code. Close the VB Editor. Select the sheet you want this to work
on and
[Run] the macro from Tools -- Macro -- Macros (Excel 2003 &
earlier) or
from the [Developer] tab in Excel 2007.

Sub MarkRows()
'works with the selected sheet
'the next Const identifies the
'column that will always have
'some entry in it on every row used
'change as required
Const keyCol = "A"
Dim lastRow As Long
Dim lastColAddr As String
Dim testRange As Range
Dim anyTestCell As Range
Dim matchCount As Double
Dim LC As Integer
Dim testCells As String
Dim matchCell As String

lastRow = Range(keyCol & Rows.Count).End(xlUp).Row
'change "1:" to "2:" if you have labels in row 1
Set testRange = Range(keyCol & "1:" & _
keyCol & lastRow)
Application.ScreenUpdating = False
For Each anyTestCell In testRange
anyTestCell.EntireRow.Interior.ColorIndex = xlNone
lastColAddr = anyTestCell.Offset(0, Columns.Count - _
anyTestCell.Column).End(xlToLeft).Address
matchCount = 0 ' reset
If Range(lastColAddr).Column 2 Then
For LC = 1 To Range(lastColAddr).Column
testCells = "A" & anyTestCell.Row & ":" & _
lastColAddr
matchCell = Cells(anyTestCell.Row, LC).Address
matchCount = _
WorksheetFunction.CountIf(Range(testCells), _
Range(matchCell))
If matchCount = 3 Then
Exit For
End If
Next
End If
If matchCount = 3 Then
anyTestCell.EntireRow.Interior.ColorIndex = 6 ' yellow
End If
Next
Set testRange = Nothing
End Sub

"Stefano" wrote:
Hi to everyone in this forum!
I'm really driving mad to find out a way to highlight any row in my
worksheet that has 3 same value.
A value can be both numbers or text.

Does anyone to know how to write a piece of VB code to accomplish
that
job with Excel 2003?

Any help will be appreciated!
Bye,
Stefano.
.

@Don Guillett and @JLatham
thanks both of you for the reply!

that's an example to you:
---------------------------------------------------------------------------------------------------------
A B D
--------------------------------------
John 3 100,50
--------------------------------------
John 3 100,50 -----------------------------------
(that's a duplicate and it has to be yellow highlighted)
--------------------------------------
John 3 200,70 -----------------------------------
(that's NOT a duplicate, because column C has a different value)
--------------------------------------
John 5 200,70 -----------------------------------
(that's NOT a duplicate, because column C has a different value)
--------------------------------------
Carl 5 900,20 -----------------------------------
(that's NOT a duplicate, because column C has a different value)
--------------------------------------

the very last thing is:
it will be great if I could run the macro only on selected rows and
not to all the worksheet.

P.S.
@JLatham
I fired your code it's work but it's not seem to highlight what I want

Thank you very much indeed for your help, i really appreciate that
Stefano.
.

@Don Guillett
I tried your code, but it does not seem to work...as expected
Please let me know more info about that
My needs is always the same:
highlight a row with at least three matching value
Thanks for your help!

@Rick Rothstein
your code looks like very handy to me, because it has the option to
choose only the rows I want to check
But...I can't make it works properly
Please, Let me know more about it!
Thanks to you too!

Stefano.

@Rick Rothstein
just column A B D. Order is not important
Thanks, Stefano.