VBA to highlight cells

  • Thread starter Thread starter Amcdee
  • Start date Start date
A

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

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
 
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
 
Back
Top