CODEWITHSUNDEEP
vbaexcelcolorsconditional-formatting
CairoCoder
CairoCoder

Reputation: 3189

How color excel column based on duplicate cloumn values

I have an Excel column called order#, that has order numbers with repeated values, as each order has one or more item, so if the order has 3 item, the order# would be repeated three times and so on.

I want to use two colors to separate between different orders, so if the first order# is "1" it would be red, and second "2" would be yellow, and third "3" would be red again, and fourth "4" would be yellow, and so on.

+----------+--------+
|  order#  |  item# |
+----------+--------+
|    1     |   11   |
|    2     |   12   |
|    2     |   22   |
+----------+--------+

Upvotes: 2

Views: 157

Answers (4)

Amlia Amber
Amlia Amber

Reputation: 1

Click on the column you want to change color. Chose formatting and format column. You can edit or customize all properties at one place. Excel templates offer huge number of templates designed using Microsoft Excel. Hope vising this will help you understand excel in relation to pre-design templates.

Upvotes: 0

Vasily
Vasily

Reputation: 5782

try this:

Sub test()
    Dim i&, x&, cl As Range, Dic As Object
    Set Dic = CreateObject("Scripting.Dictionary"): Dic.CompareMode = vbTextCompare
    i = [A:A].Find("*", , xlValues, , xlByRows, xlPrevious).Row
    x = [1:1].Find("*", , xlValues, , xlByColumns, xlPrevious).Column
    For Each cl In Range("A2:A" & i)
        If Not Dic.exists(cl.Value2) Then
            Dic.Add cl.Value2, IIf(Dic.Count Mod 2 = 0, vbRed, vbYellow)
        End If
    Next cl
    For Each cl In Range("A2:A" & i)
        Range(cl, Cells(cl.Row, x)).Interior.Color = Dic(cl.Value2)
    Next cl
End Sub

Upvotes: 1

R3uK
R3uK

Reputation: 14537

This should do the trick :

Sub test_CairoCoder()
Dim wS As Worksheet, _
    LastRow As Long, _
    ColorChg As Boolean, _
    OrderNb As String

Set wS = ActiveSheet
ColorChg = False

With wS
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    OrderNb = wS.Cells(2, 1)
    For i = 2 To LastRow
        If .Cells(i, 1) <> .Cells(i + 1, 1) And .Cells(i, 1) <> .Cells(i - 1, 1) Then
            ColorChg = Not ColorChg
            If ColorChg Then
                .Range(.Cells(i, "A"), .Cells(i + 1, "A")).Interior.Color = vbRed
            Else
                .Range(.Cells(i, "A"), .Cells(i + 1, "A")).Interior.Color = vbYellow
            End If
        Else
            If .Cells(i, 1) <> .Cells(i + 1, 1) Then
            Else
                If OrderNb <> .Cells(i, 1) Then
                    OrderNb = .Cells(i, 1)
                    ColorChg = Not ColorChg
                Else
                End If

                If ColorChg Then
                    .Range(.Cells(i, "A"), .Cells(i + 1, "A")).Interior.Color = vbRed
                Else
                    .Range(.Cells(i, "A"), .Cells(i + 1, "A")).Interior.Color = vbYellow
                End If
            End If
        End If
    Next i
End With
MsgBox "All done!", vbInformation + vbOKOnly

End Sub

Upvotes: 1

Dirk Reichel
Dirk Reichel

Reputation: 7979

stay with conditional formating like:

=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)=0
'and the other color
=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)=1

no need for vba or running a macro every time you change something to your list copy that formula for range like A2:B100

Upvotes: 2

Related Questions