VBA to highlight cells

[Amcdee]

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

 

[Bob Phillips]

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)

 

[CoRrRan]

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

 

[Mike H]

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

 

[Mike H]

Of course i meant

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



Mike

 

[Amcdee]

Of course i meant

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



Mike






- Show quoted text -

Thanks guys for your help just what I needed.