On Jan 11, 2:56*pm, AB wrote:
:)
Obviously this one:
Set RangeToCheck = ActiveSheet.Columns("A").
was supposed to be:
Set RangeToCheck = ActiveSheet.Columns(myColumn).

As otherwise there was no point in the constant...

On Jan 11, 9:48*am, AB wrote:

Might give this a shot:
Sub ColorTheSameValueCells()
* * Const myColumn As String = "A" '<--column whose cells to be
compared
* * Dim RangeToCheck As Range
* * Dim oneCell As Range, oneCell2 As Range
* * Dim currentValue As Long '<--assumed that values are always of
LONG type
* * Dim currentColorIndex As Long '<--holds color index

* * Set RangeToCheck = ActiveSheet.Columns("A"). _
* * * * SpecialCells(xlCellTypeConstants) '<-- will loop through every
_
* * * * * * cell in the column that contains a value

* * RangeToCheck.Interior.ColorIndex = xlNone 'Reset color to none

* * For Each oneCell In RangeToCheck
* * * * If oneCell.Interior.ColorIndex = xlNone Then
* * * * * * currentValue = oneCell.Value
* * * * * * On Error GoTo RunOutOfColors:
* * * * * * currentColorIndex = currentColorIndex + 1 '<-- might _
* * * * * * * * * * run out of colors if there are too many different
values
* * * * * * On Error GoTo 0
* * * * * * For Each oneCell2 In RangeToCheck
* * * * * * * * With oneCell2
* * * * * * * * * * If .Value = currentValue Then
* * * * * * * * * * * * .Interior.ColorIndex = currentColorIndex
* * * * * * * * * * End If
* * * * * * * * End With
* * * * * * Next oneCell2
* * * * End If
* * Next oneCell

* * Exit Sub
RunOutOfColors:
* * oneCell.Select
* * MsgBox "Run out of colors on the selected cell!", vbExclamation

End Sub

On Jan 11, 7:41*am, noname wrote:

On Jan 11, 12:40*pm, noname wrote:

Hi,
i have a column which has values like:
col A
1
1
1
2
2
2
2
3
3
4
4
4

i want to compare each cell with other cells in column and see if they
are same, if so color similar cells with the same color. so you have
groups of similar cells colored the same.

this has to be implemented in a code, so i need a VBA solution and not
a worksheet solution.

Thanks.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thanks. it works.
i want to implement this for the data series groups for an XY Scatter
chart, so the points of each data series groups are colored the same.

Any ideas on how to do this?