cells colour

[clarksonneo]

Hi,

I need a macro which can produce the result like the picture below.

For all numbers I selected,

if the number appears at the first time (eg cell A3), the cell colour will be green,

if the number appears more than 1 time (eg cell A4, A5), the cell colour will be yellow.


How can I achieve it?

Thanks

[Kenneth Hobs]

If your data is sorted then:
Sub SelectionGreenFirst()
Dim cell As Range
For Each cell In Selection
If cell.Value = Empty Then GoTo NextCell
If cell.Offset(-1).Value = cell.Value Then
cell.Interior.Color = vbYellow
Else: cell.Interior.Color = vbGreen
End If
NextCell:
Next cell
End Sub

[mdmackillop]

You could also use conditional formatting - see attached