Color duplicates rows in two different sets of data and also the rows which are present in both sets of data.

[Noob_Excel]

Note - When I say rows, I mean cells in column A only and not the whole row.

Background

There are two sets of data. Lets call the data on the top, Data A and data on the bottom, Data B

I have made a Macro (VBA provided at the bottom) that:
1. Clears the Conditional Formatting on the entire sheet.
2. Highlights all duplicate rows as Red.
3. Highlights all duplicate rows in Data A as Green.
4. Highlights all non-empty cells in Column E as Yellow.
5. Sorts Data A in the following order. Red cells in Column A, Green cells in Column A, Yellow cells in Column E, Column A Values Ascending, Column D Values Ascending.

In simple words it;
a) marks duplicate rows present in both Data A & Data B as red
b) marks duplicate rows of Data A as Green.

In the image below: Data A(A6:A8)=Data B(A18,A20) Data A(A9:A10)=Data B(A19) Data A(A4:A5)

Question

Now I want the Macro to work this way:
1) Duplicate rows of whole data should be Highlighted Red
2) Duplicate rows in Data A that are also present in Data B should all be Highlighted Blue
3) Duplicate rows present only in Data A should be Highlighted Green
4) Any non-blank cells in Column E should be Yellow

Sample Data Image

Personal.xlsb
	A	B	C	D	E
1	661-067-6333	2020 honda civic	17,900	Los angeles	title status salvage
2	661-585-8368	2002 Buick Lesabre	2,800	Grand Rapids
3	998-216-0204	2003 chevrolet silverado	3,200	Perrinton
4	226-017-1780	2006 HONDA ACCORD	3,900	Rochester hill	title status Lemon
5	226-017-1780	2019 Chevrolet Silverado 3500 LT	48,500	West Bend
6	568-072-2522	2007 Chevrolet HHR LT	4,200	Sterling Heights
7	568-072-2522	1999 Ford Ranger	3,300	Sterling Heights
8	568-072-2522	2006 Toyota Camry XLE	2,999	Sterling Heights
9	737-958-0293	2015 Jeep Grand Cherokee	21,700	Fenton
10	737-958-0293	2016 Chevrolet Equinox V6	16,900	Trevor WI nearby Antioch IL
11	662-134-8725	2020 honda civic ex hatchback	17,900	Antioch IL	title status salvage
12	213-407-9351	2008 Chrysler PT cruiser	6,850	montague
13	242-488-0805	2003 HONDA ACCORD V6 EX-L	3,200	schiller
14	284-877-4409	2010 chevrolet silverado 1500	11,250	WEST BLOOMFIELD
15
16
17	568-072-2522	2006 toyota camry	2999	Sterling Heights
18	737-958-0293	2013 toyota prius	2999	Fenton
19	568-072-2522	2008 ford f-150	5900	Sterling Heights	title status rebuilt
20	616-108-5898	1999 oodge Ram 2500 2W0	2800	Grand Rapids
21	998-216-0204	2006 Honda Accord	3200	Perrinton
22	881-884-6206	2018 Nissan Rogue	12900	Sun valley
23	661-067-6333	2014 Ford explorer XLT	8750	Trevor WI	title status salvage
24	801-229-4503	2009 Audi A3	4900	Simi Valley
25	616-695-0075	2005 Honda Accord	1500	Tehachapi
26	616-013-8792	Nissan Frontier 2004 extra cab	4800	Santa Clarita
27	616-013-8792	2014 Ford Taurus Police Intercepter	10200	Bakersfield
28	661-585-8368	2002 Buick Lesabre	11000	Grand Rapids
29	973-765-4366	2006 land rover range rover	7600	charlotte
30	595-304-9290	2008 Chrysler PT cruiser	4000	Fresno	title status lemon
31	526-467-3561	2000 Toyota 4Runner	5850	Long Beach	title status rebuilt
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E1:E31	Expression	=LEN(TRIM(E1))>0	text	NO
A1:A14	Cell Value	duplicates	text	NO
A:A	Cell Value	duplicates	text	NO

[Sample Data](https://i.stack.imgur.com/QzQc3.png)

This is the VBA of my Macro:

'
'
'Declaration
'
'
    Dim MyRange As String
    Dim Rough As String
    Dim A_To_Q As String
    Dim A_To_E As String
    Dim A_To_F As String
    Dim ColumnA As String
    Dim ColumnC As String
    Dim ColumnD As String
    Dim ColumnE As String
    Dim ColumnF As String
'
'
'Assignment
'
'
    MyRange = ActiveCell.Address(0, 0) & ":" & "E1"
'
    Rough = ActiveCell.Offset(0, -2).AddressLocal & ":" & "Q1"
    A_To_Q = Mid(Rough, 2, 1) & Mid(Rough, 4, 6)
    Rough = ActiveCell.Offset(0, -2).Address & ":" & "E1"
    A_To_E = Mid(Rough, 2, 1) & Mid(Rough, 4, 6)
    Rough = ActiveCell.Offset(0, -2).Address & ":" & "F1"
    A_To_F = Mid(Rough, 2, 1) & Mid(Rough, 4, 6)
    Rough = ActiveCell.Offset(0, -2).Address & ":" & "A1"
    ColumnA = Mid(Rough, 2, 1) & Mid(Rough, 4, 6)
    Rough = ActiveCell.Offset(0, 0).Address & ":" & "C1"
    ColumnC = Mid(Rough, 2, 1) & Mid(Rough, 4, 6)
    Rough = ActiveCell.Offset(0, 1).Address & ":" & "D1"
    ColumnD = Mid(Rough, 2, 1) & Mid(Rough, 4, 6)
    Rough = ActiveCell.Offset(0, 2).Address & ":" & "E1"
    ColumnE = Mid(Rough, 2, 1) & Mid(Rough, 4, 6)
    Rough = ActiveCell.Offset(0, 3).Address & ":" & "F1"
    ColumnF = Mid(Rough, 2, 1) & Mid(Rough, 4, 6)
'
'
'Formating
'
'
    Cells.FormatConditions.Delete
'
    Columns("A:A").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
'
    Range(ColumnA).Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
'
    Range(ColumnE).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(E1))>0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16751204
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10284031
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
'
'
'Sorting
'
'
Range(A_To_F).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range(ColumnA), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        199, 206)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range(ColumnA), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(198, _
        239, 206)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range(ColumnE), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
        235, 156)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(ColumnA) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(ColumnD) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(A_To_F)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With