Hi folks,

hope you can help with a little problem. The has to be achieved using
VBA so please don't suggest conditional formatting as it wont achieve
what is required.

we have a sheet in the following format:


A B C D
101 10A "Some String" "Some String"
101 10A "Some String" "Some String"
101 50A "Some String" "Some String"
303 50A "Some String"
303 50A "Some String" "Some String"
303 1152A "Some String" "Some String"


Ok here is the problem... we wish to highlight (color) the background
of each row that has identical vales in column B. The sheet auto
updates from a data source, and is sorted on the values in column B,
therefore all identical values will remain together.

Any help would be appreciated as this is driving me mad.

Many thanks in advance

Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim mpLastRow As Long


With ActiveSheet

For i = 2 To mpLastRow
If Application.CountIf(.Columns(2), .Cells(i,
TEST_COLUMN).Value) 1 Then
.Rows(i).Interior.ColorIndex = 3
End If
Next i

End With

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Amcdee" wrote in message
oups.com...
Hi folks,

hope you can help with a little problem. The has to be achieved using
VBA so please don't suggest conditional formatting as it wont achieve
what is required.

we have a sheet in the following format:


A B C D
101 10A "Some String" "Some String"
101 10A "Some String" "Some String"
101 50A "Some String" "Some String"
303 50A "Some String"
303 50A "Some String" "Some String"
303 1152A "Some String" "Some String"


Ok here is the problem... we wish to highlight (color) the background
of each row that has identical vales in column B. The sheet auto
updates from a data source, and is sorted on the values in column B,
therefore all identical values will remain together.

Any help would be appreciated as this is driving me mad.

Many thanks in advance

Amcdee wrote:
Hi folks,

hope you can help with a little problem. The has to be achieved using
VBA so please don't suggest conditional formatting as it wont achieve
what is required.

we have a sheet in the following format:


A B C D
101 10A "Some String" "Some String"
101 10A "Some String" "Some String"
101 50A "Some String" "Some String"
303 50A "Some String"
303 50A "Some String" "Some String"
303 1152A "Some String" "Some String"


Ok here is the problem... we wish to highlight (color) the background
of each row that has identical vales in column B. The sheet auto
updates from a data source, and is sorted on the values in column B,
therefore all identical values will remain together.

Any help would be appreciated as this is driving me mad.

Many thanks in advance


Other solution:

Dim i As Integer, j As Integer

Range("B1").Activate
i = 1: j = 1
Do
If Range("B1").Offset(i, 0).Value = _
Range("B1").Offset(i - 1, 0).Value Then
Range("B1").Offset(i - 1, 0).Resize(2,1).EntireRow. _
Interior.ColorIndex = j
End If
If j = 11 Then j = 12 Else j = 11 ' Colors for row highlighting
i = i + 1
Loop Until Range("B1").Offset(i, 0).Value = ""

CoRrRan
--
Change NOSPAM to GMAIL

Try,

Sub bandit()
icolour = 3
lastrow = Range("b65536").End(xlUp).Row
Cells(1, 2).Select
ActiveCell.EntireRow.Select
Selection.Interior.ColorIndex = icolour
For i = 2 To lastrow
Cells(i, 2).Select
If Cells(i, 2).Value = Cells(i, 2).Offset(1, 0).Value Then
ActiveCell.EntireRow.Select
Selection.Interior.ColorIndex = icolour
Else
icolour = icolour + 1
ActiveCell.EntireRow.Select
Selection.Interior.ColorIndex = icolour
End If
Next
End Sub

Mike

"Amcdee" wrote:

Hi folks,

hope you can help with a little problem. The has to be achieved using
VBA so please don't suggest conditional formatting as it wont achieve
what is required.

we have a sheet in the following format:


A B C D
101 10A "Some String" "Some String"
101 10A "Some String" "Some String"
101 50A "Some String" "Some String"
303 50A "Some String"
303 50A "Some String" "Some String"
303 1152A "Some String" "Some String"


Ok here is the problem... we wish to highlight (color) the background
of each row that has identical vales in column B. The sheet auto
updates from a data source, and is sorted on the values in column B,
therefore all identical values will remain together.

Any help would be appreciated as this is driving me mad.

Many thanks in advance

Of course i meant

If Cells(i, 2).Value = Cells(i, 2).Offset(-1, 0).Value Then

:)

Mike

"Mike H" wrote:

Try,

Sub bandit()
icolour = 3
lastrow = Range("b65536").End(xlUp).Row
Cells(1, 2).Select
ActiveCell.EntireRow.Select
Selection.Interior.ColorIndex = icolour
For i = 2 To lastrow
Cells(i, 2).Select
If Cells(i, 2).Value = Cells(i, 2).Offset(1, 0).Value Then
ActiveCell.EntireRow.Select
Selection.Interior.ColorIndex = icolour
Else
icolour = icolour + 1
ActiveCell.EntireRow.Select
Selection.Interior.ColorIndex = icolour
End If
Next
End Sub

Mike

"Amcdee" wrote:

Hi folks,

hope you can help with a little problem. The has to be achieved using
VBA so please don't suggest conditional formatting as it wont achieve
what is required.

we have a sheet in the following format:


A B C D
101 10A "Some String" "Some String"
101 10A "Some String" "Some String"
101 50A "Some String" "Some String"
303 50A "Some String"
303 50A "Some String" "Some String"
303 1152A "Some String" "Some String"


Ok here is the problem... we wish to highlight (color) the background
of each row that has identical vales in column B. The sheet auto
updates from a data source, and is sorted on the values in column B,
therefore all identical values will remain together.

Any help would be appreciated as this is driving me mad.

Many thanks in advance

On Jul 27, 10:56 am, Mike H wrote:
Of course i meant

If Cells(i, 2).Value = Cells(i, 2).Offset(-1, 0).Value Then

:)

Mike



"Mike H" wrote:
Try,

Sub bandit()
icolour = 3
lastrow = Range("b65536").End(xlUp).Row
Cells(1, 2).Select
ActiveCell.EntireRow.Select
Selection.Interior.ColorIndex = icolour
For i = 2 To lastrow
Cells(i, 2).Select
If Cells(i, 2).Value = Cells(i, 2).Offset(1, 0).Value Then
ActiveCell.EntireRow.Select
Selection.Interior.ColorIndex = icolour
Else
icolour = icolour + 1
ActiveCell.EntireRow.Select
Selection.Interior.ColorIndex = icolour
End If
Next
End Sub

Mike

"Amcdee" wrote:

Hi folks,

hope you can help with a little problem. The has to be achieved using
VBA so please don't suggest conditional formatting as it wont achieve
what is required.

we have a sheet in the following format:

A B C D
101 10A "Some String" "Some String"
101 10A "Some String" "Some String"
101 50A "Some String" "Some String"
303 50A "Some String"
303 50A "Some String" "Some String"
303 1152A "Some String" "Some String"

Ok here is the problem... we wish tohighlight(color) the background
of each row that has identical vales in column B. The sheet auto
updates from a data source, and is sorted on the values in column B,
therefore all identical values will remain together.

Any help would be appreciated as this is driving me mad.

Many thanks in advance- Hide quoted text -

- Show quoted text -

Thanks guys for your help just what I needed. :D