in eccel 2003- 3 duplicate values in column to be coloured

[rupashree]

in a sorted column containing 3 sequential duplicate values, the cells need to be coloured.others should be left without colouring.please provide a macro.the cells much be coloured only that contains 3 duplicate values

<table width="100" border="0" cellpadding="0" cellspacing="0"><col style="width: 75pt;" width="100"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 75pt;" width="100" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" height="17">
</td> </tr> </tbody></table>

 

[JackDanIce]

Welcome to the board.

What column is your data in?

What happens if you have values
A1: 1
A2: 1
A3: 1
A4: 1

Should A1:A3 be coloured and not A4? Or should A1:A3 be coloured and then A2:A4 be coloured?

Try:

Sub FindLastColumn()
 
Dim i As Long, j As Long
 
Application.ScreenUpdating = False
 
'With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
'    .Sort key1:=Range("A1"), order1:=xlAscending, header:=xlGuess
'End With
 
i = 1
Do While Not IsEmpty(Range("A" & i))
    If Range("A" & i) = Range("A" & i + 1) And Range("A" & i) = Range("A" & i + 2) Then
        Range("A" & i & ":A" & i + 2).Interior.ColorIndex = 3
    End if        
    i = i + 1
Loop
 
Application.ScreenUpdating = True
 
End Sub

If you remove the first character ' from lines 4, 5 and 6 above, it will sort the column in ascending order first

 

[rupashree]

A1:A3 should be coloured

 

[JackDanIce]

Change my code to:

Sub FindLastColumn()
 
Dim i As Long, j As Long
 
Application.ScreenUpdating = False
 
'With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' .Sort key1:=Range("A1"), order1:=xlAscending, header:=xlGuess
'End With
 
i = 1
Do While Not IsEmpty(Range("A" & i))
  If Range("A" & i) = Range("A" & i + 1) And Range("A" & i) = Range("A" & i + 2) Then
    Range("A" & i & ":A" & i + 2).Interior.ColorIndex = 3
    i = i + 3
  Else
    i = i + 1
  End if 
Loop
 
Application.ScreenUpdating = True
 
End Sub

 

[rupashree]

my excel is not been coloured at all.i copied the code into the visyal basic editor

 

[JackDanIce]

What column is your data in? I have my test data in column A and it works fine, it colours consequtive cells of three the colour red if the values are the same.

 

[rupashree]

sorry data was in H column. its working fine in first column.....thanks a lot

 

[JackDanIce]

You're welcome

 

[rupashree]

You're welcome

can i get alternative colours for each set(3 duplicate cells is a single set)

 

[rupashree]

can i get alternative colours for each set(3 duplicate cells is a single set)

please i need code for alternative colours......

two colours is enough ....i need light colours