trying to highlight records with at least 3 same value

S

Stefano

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

JLatham

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
 
S

Stefano

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 said:
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.
Click to expand...
Does anyone to know how to write a piece of VB code to accomplish that
job with Excel 2003?
Click to expand...
Any help will be appreciated!
Bye,
Stefano.
.
Click to expand...
Click to expand...

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

Don Guillett

sub colormatchingrowsSAS()
for i = 4 to 44 'rows to check
if cells(i,1)=cells(i+1,1) and _
cells(i,2)=cells(i+2,1) and +
cells(i,3)=cells(i+3,1) then
cells(i,1).resize(,3).interior.colorindex=6
end if
next i
end sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Stefano said:
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 said:
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.
Click to expand...
Does anyone to know how to write a piece of VB code to accomplish that
job with Excel 2003?
Click to expand...
Any help will be appreciated!
Bye,
Stefano.
.
Click to expand...
Click to expand...

@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.
Click to expand...
 
D

Don Guillett

typo
sub colormatchingrowsSAS()
for i = 4 to 44 'rows to check
if cells(i,1)=cells(i+1,1) and _ 'space between d and _
cells(i,2)=cells(i+2,1) and _
cells(i,3)=cells(i+3,1) then
cells(i,1).resize(,3).interior.colorindex=6
end if
next i
end sub
Click to expand...

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don Guillett said:
sub colormatchingrowsSAS()
for i = 4 to 44 'rows to check
if cells(i,1)=cells(i+1,1) and _
cells(i,2)=cells(i+2,1) and +
cells(i,3)=cells(i+3,1) then
cells(i,1).resize(,3).interior.colorindex=6
end if
next i
end sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Stefano said:
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

:
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.
.
Click to expand...

@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.
Click to expand...
Click to expand...
 
R

Rick Rothstein

You can give this a try. Select one or more columns of cells covering the
rows you want to apply the macro to and then run this macro...

Sub HighlightTriplets()
Dim R As Range, B As Long, E As Long
B = Selection(1).Row 'row number of first row in Selection
E = Selection(Selection.Count).Row 'row number of last row in Selection
For Each R In Selection.Rows
If Evaluate("=SUMPRODUCT(($A$" & B & ":$A$" & E & "&""/""&$B$" & B & _
":$B$" & E & "&""/""&" & "$D$" & B & ":$D$" & E & "=$A" & _
R.Row & "&""/""&$B" & R.Row & "&""/""&$D" & R.Row & _
")*($A$" & B & ":$A$" & E & "&$B$" & B & ":$B$" & E & _
"&$D$" & B & ":$D$" & E & "<>""""))>=3") Then
R.EntireRow.Interior.ColorIndex = 6
End If
Next
End Sub

A note about the selection method. Let's say you want to run the macro
against rows 4 through 30... you could select the entire rows 4:30, or you
could select A4:A30, or you could select C4:F30, etc. The actual cells you
select doesn't matter just as long as they cover the range of rows you want
to apply the macro to.

--
Rick (MVP - Excel)



Stefano said:
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 said:
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.
Click to expand...
Does anyone to know how to write a piece of VB code to accomplish that
job with Excel 2003?
Click to expand...
Any help will be appreciated!
Bye,
Stefano.
.
Click to expand...
Click to expand...

@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.
Click to expand...
 
J

JLatham

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 said:
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 said:
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.
Click to expand...
Does anyone to know how to write a piece of VB code to accomplish that
job with Excel 2003?
Click to expand...
Any help will be appreciated!
Bye,
Stefano.
.
Click to expand...
Click to expand...

@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.
.
Click to expand...
 
S

Stefano

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 said:
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
:
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.
.
Click to expand...
Click to expand...
@Don Guillett and @JLatham
thanks both of you for the reply!
Click to expand...
that's an example to you:
---------------------------------------------------------------------------------------------------------
A B D
Click to expand...
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.
Click to expand...
P.S.
@JLatham
I fired your code it's work but it's not seem to highlight what I want
Click to expand...
Thank you very much indeed for your help, i really appreciate that
Stefano.
.
Click to expand...
Click to expand...

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

Rick Rothstein

@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!
Click to expand...

I may have misunderstood what you are looking to do. I took your example as
gospel and code my macro to look **only** in Columns A, B and D, but in
re-reading your original message, I'm not so sure you want the columns being
looked at so restricted. Can you clarify exactly what is to be searched? If
not just Columns A, B and D, but rather all columns, then is the order of
the items important?
 
D

Don Guillett

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
(e-mail address removed)
Stefano said:
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 said:
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?
Click to expand...
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.
Click to expand...
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.
Click to expand...
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
Click to expand...
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
Click to expand...
:
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.
Click to expand...
Does anyone to know how to write a piece of VB code to accomplish
that
job with Excel 2003?
Click to expand...
Any help will be appreciated!
Bye,
Stefano.
.
Click to expand...
@Don Guillett and @JLatham
thanks both of you for the reply!
Click to expand...
that's an example to you:
---------------------------------------------------------------------------------------------------------
A B D
Click to expand...
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.
Click to expand...
P.S.
@JLatham
I fired your code it's work but it's not seem to highlight what I want
Click to expand...
Thank you very much indeed for your help, i really appreciate that
Stefano.
.
Click to expand...
Click to expand...

@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.
Click to expand...
 
S

Stefano

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

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.
:
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
:
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.
.
Click to expand...
Click to expand...
@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!
Click to expand...
@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!
Click to expand...
Stefano.
Click to expand...
Click to expand...

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

Don Guillett

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Stefano said:
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

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."
Click to expand...
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.
Click to expand...
Hopefully, one of the other offers of help hits the mark closer.
Click to expand...
:
On Apr 4, 7:45 pm, JLatham <[email protected]>
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?
Click to expand...
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.
Click to expand...
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.
Click to expand...
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
Click to expand...
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
Click to expand...
:
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.
Click to expand...
Does anyone to know how to write a piece of VB code to
accomplish
that
job with Excel 2003?
Click to expand...
Any help will be appreciated!
Bye,
Stefano.
.
Click to expand...
@Don Guillett and @JLatham
thanks both of you for the reply!
Click to expand...
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)
--------------------------------------
Click to expand...
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.
Click to expand...
P.S.
@JLatham
I fired your code it's work but it's not seem to highlight what I
want
Click to expand...
Thank you very much indeed for your help, i really appreciate that
Stefano.
.
Click to expand...
@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!
Click to expand...
@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!
Click to expand...
Stefano.
Click to expand...
Click to expand...

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

Don Guillett

If I understand correctly, you can just change my code to look at column 4
instead of column3
sub colormatchingrowsSAS()
for i = 4 to 44 'rows to check
if cells(i,1)=cells(i+1,1) and _
cells(i,2)=cells(i+2,1) and _
cells(i,4)=cells(i+4,1) then
cells(i,1).resize(,4).interior.colorindex=6
end if
next i
end sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Stefano said:
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

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."
Click to expand...
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.
Click to expand...
Hopefully, one of the other offers of help hits the mark closer.
Click to expand...
:
On Apr 4, 7:45 pm, JLatham <[email protected]>
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?
Click to expand...
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.
Click to expand...
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.
Click to expand...
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
Click to expand...
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
Click to expand...
:
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.
Click to expand...
Does anyone to know how to write a piece of VB code to
accomplish
that
job with Excel 2003?
Click to expand...
Any help will be appreciated!
Bye,
Stefano.
.
Click to expand...
@Don Guillett and @JLatham
thanks both of you for the reply!
Click to expand...
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)
--------------------------------------
Click to expand...
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.
Click to expand...
P.S.
@JLatham
I fired your code it's work but it's not seem to highlight what I
want
Click to expand...
Thank you very much indeed for your help, i really appreciate that
Stefano.
.
Click to expand...
@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!
Click to expand...
@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!
Click to expand...
Stefano.
Click to expand...
Click to expand...

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

Rick Rothstein

@Rick Rothstein
just column A B D. Order is not important
Click to expand...

If by that you mean this...

A B D
=== === ===
Rick Stefano Don

matches this....

A B D
=== === ===
Don Stefano Rick

then my posted code will not work for you.
 
S

Stefano

If by that you mean this...

A B D
=== === ===
Rick Stefano Don

matches this....

A B D
=== === ===
Don Stefano Rick

then my posted code will not work for you.
Click to expand...

@Rick Rothstein
no...I'll always have "Rick"at A column.... but it still does not seem
to work...why?
Please let me know more...
I mean, if I start a new sheet and I write Rick, Stefano, Don,
respectively into column A, B, D, and i run the macro nothing became
highlighted...?
What I'm doing wrong?

Thank you very much indeed,
Stefano.
 
D

Don Guillett

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

Rick Rothstein

I'm not sure what to tell you... if we are looking in Columns A, B and D for
identical content in each of the cells for a given column occurring 3 or
more time, then the code I posted works fine in my tests. Are you
remembering to select a range whose rows cover the rows you want to test for
your triples? You can send your workbook to me if you want and I would be
happy to look and see why you are having trouble with my code.... just
remove the NO.SPAM stuff from my email address.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sheet name=Cell value 2
Compare two worksheets in the same workbook and highlight difference 7
macro to highlight rows that meet specific conditions 1
looping through columns 3
VBA to highlight cells 5
VBA - Run same logic on values in columns 2 & 3 0
Find records that belong to a combo box value 1
Challenging 10

Top