Conditional formatting

[mahmoud-lee]

I need your help in this work, and hope you have a solution by the formula

[mahmoud-lee]

I think this is the solution
=or(countif(list1;a1)>0;countif(list2;a1)>1;counti f(list3;a1)>1;countif(list4;a1)>1;countif(list5;a1 )>1)
and other formatt
=or(countif(list1;a1)>1;countif(list2;a1)>0;counti f(list3;a1)>0;countif(list4;a1)>0;countif(list5;a1 )>0)

[Excel Fox]

Can you please explain the logic first. I see a few cells colored when they break the numbering sequence. But I also see some inconsistencies in the ordering of the green cells also. Can you please clarify that?

[mahmoud-lee]

This table I think more clarification

[LalitPandey87]

Not clearly mentioned in the file only found some colored cells and don't understand by which criteria.

[Admin]

Hi,

Try this macro.

Option Explicit

Sub kTest()
    
    Dim d, r As Long, c As Long, n As Long, Rng As Range
    Dim dic As Object, a() As String, x, y, Colors, j As Long
    
    Set Rng = Range("a1:j12")                   '<<< adjust the range
    d = Rng.Value2
    
    Colors = Array(255, 65535, 52479, 8388736)  '<<< add as many as interior colors
    
    Set dic = CreateObject("scripting.dictionary")
        dic.comparemode = 1
    x = Application.Index(d, 0, 1)
    n = -1
    For r = 1 To UBound(d, 1)
        For c = 3 To UBound(d, 2) Step 2
            If Len(d(r, c)) Then
                y = Application.Match(d(r, c), x, 0)
                If IsError(y) Then
                    If Not dic.exists(d(r, c)) Then
                        n = n + 1: dic.Item(d(r, c)) = Colors(n)
                        j = j + 1: ReDim Preserve a(1 To j)
                        a(j) = Rng.Cells(r, c + 1).Address & "|" & Colors(n)
                    Else
                        j = j + 1: ReDim Preserve a(1 To j)
                        a(j) = Rng.Cells(r, c + 1).Address & "|" & dic.Item(d(r, c))
                    End If
                End If
            End If
        Next
    Next
    
    x = Array(dic.keys, dic.items)
    
    With Rng
        For r = 0 To UBound(x(0))
            Application.ReplaceFormat.Interior.Color = x(1)(r)
            .Replace What:=x(0)(r), Replacement:=x(0)(r), LookAt:=xlWhole, ReplaceFormat:=True
        Next
        For r = 1 To j
            y = Split(a(r), "|")
            .Range(y(0)).Interior.Color = y(1)
        Next
    End With

End Sub

[Rick Rothstein]

Dim d, r As Long, c As Long, n As Long, Rng As Range
Dim dic As Object, a() As String, x, y, Colors, j As Long
[/code]

Admin... I think you forgot for the moment you were writing VBA code... the d, x, y and Colors variable will all be declared as Variants for the above Dim statements. For those reading this message... in VBA, each variable must be individually declared as to its data type, otherwise it will be declared as a Variant. So, Admin meant to write the above like this...

Dim d As Long, r As Long, c As Long, n As Long, Rng As Range
Dim dic As Object, a() As String, x As Long, y As Long, Colors As Long, j As Long

[Excel Fox]

Rick, I wouldn't be sure , except for j maybe

Set Rng = Range("a1:j12")                   '<<< adjust the range
    d = Rng.Value2
Colors = Array(255, 65535, 52479, 8388736)
x = Application.Index(d, 0, 1)
x = Array(dic.keys, dic.items)
y = Split(a(r), "|")

[Admin]

Admin... I think you forgot for the moment you were writing VBA code... the d, x, y and Colors variable will all be declared as Variants for the above Dim statements. For those reading this message... in VBA, each variable must be individually declared as to its data type, otherwise it will be declared as a Variant. So, Admin meant to write the above like this...

Dim d As Long, r As Long, c As Long, n As Long, Rng As Range
Dim dic As Object, a() As String, x As Long, y As Long, Colors As Long, j As Long

That's all (d, x, y and Colors ) meant to be as Variant.

and j is declared as long

[mahmoud-lee]

wonderful but you can do it by formula