colouring the cell based on the values of other column

Shall we use Conditional Formatting?

Please confirm

Hi,

This is my approach, using macro.

Sub FilterIt()
  Dim rng As Range, cell As Range, mtx(), i As Long
  Set rng = Selection
  ReDim mtx(1 To 2, 1 To rng.Rows.Count)
  'Get uniques
  i = 0
  rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    For Each cell In rng.SpecialCells(xlCellTypeVisible)
        i = i + 1
        mtx(1, i) = cell.Value
        If i > 1 Then
           If mtx(1, i) = mtx(1, i - 1) Then
              mtx(1, i) = ""
              i = i - 1
           End If
           If mtx(1, i) = "" Then i = i - 1
        End If
    Next cell
    ReDim Preserve mtx(1 To 2, 1 To i)
  rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=False
  'Check length
  For Each cell In rng
      For i = 1 To UBound(mtx, 2)
          If cell.Value = mtx(1, i) Then
             If IsEmpty(mtx(2, i)) Then
                mtx(2, i) = Len(cell.Offset(, 1))
             Else
                If Len(cell.Offset(, 1).Value) <> mtx(2, i) Then
                   mtx(2, i) = -1
                   Exit For
                End If
             End If
          End If
      Next i
  Next cell
  'Set colour
  For Each cell In rng
      For i = 1 To UBound(mtx, 2)
          If cell.Value = mtx(1, i) Then
             If mtx(2, i) = -1 Then cell.Interior.Color = 65535
             Exit For
          End If
      Next i
  Next cell
End Sub

Select the range (A4:A18) then run the macro.
You don't even need to sort the data first, look at my attached file, and try to run at range A23:A39.

Regards

Originally Posted by excelhelpseeker

shows error at ....................mtx(1, i) = cell.Value

You ***MUST NOT*** select A2, just select A4:A18 (or A23:A39) as I told in my previous post.

Or

Sub ColorRangeOfCells()
Dim rSource As Range, r As Range, lTot As Long, lReq As Long, sR1 As String, sR2 As String

Set rSource = Application.InputBox("Select Data", "Data Seleciton Required", "=Sheet1!A4:B18", , , , , 8)

With rSource
    sR1 = "'" & .Parent.Name & "'!" & .Columns(1).Address
    sR2 = "'" & .Parent.Name & "'!" & .Columns(2).Address
End With


For Each r In rSource.Columns(1).Cells
    If r.Value <> "" Then
        e = "SUMPRODUCT((" & sR1 & "=" & r.Value & ")*LEN(" & sR2 & "))"
        lTot = Evaluate("SUMPRODUCT((" & sR1 & "=" & r.Value & ")*LEN(" & sR2 & "))")
        lReq = Evaluate("COUNTIF(" & sR1 & "," & r.Value & _
                ")*MAX(INDEX((" & sR1 & "=" & r.Value & ")*LEN(" & sR2 & "),))")
        If lTot <> lReq Then r.Resize(, rSource.Columns.Count).Interior.Color = 65535
    End If
Next r

End Sub

In your sample from Post#13 with the exception for "QUERY" in Column A all the items are unique. Why would you expect it too work? It's an entire different lay out than what you had from Post #1? If you interchange the columns, it does highlight. Not sure that's what you are looking for though?