In Excel : I’m writing a code to compare two columns in two different tables , I want to compare sheet2 with sheet1 , in case a row exist in second sheet (DetailData) and not exist in first sheet (MainData) , then the row should be colored with red in the second sheet .

in case a row DOES NOT exist in second sheet (DetailData) and exist in first sheet (MainData) , then a new row should be created in the same place in second sheet (DetailData) with background green .

I already wrote the code for the first part , but second part I did not

Sub CompareAndHighlight()
Dim mainSheet As Worksheet
Dim detailSheet As Worksheet
Dim mainData As Range
Dim detailData As Range
Dim mainDict As Object
Dim detailDict As Object
Dim cell As Range
Dim lastRowMain As Long
Dim lastRowDetail As Long
Dim detailRow As Long
Dim i As Long
Dim j As Long

Set mainSheet = ThisWorkbook.Sheets("MainData")
Set detailSheet = ThisWorkbook.Sheets("DetailData")

' Create dictionaries to store Ids and Names for quick lookup
Set mainDict = CreateObject("Scripting.Dictionary")
Set detailDict = CreateObject("Scripting.Dictionary")

' Load MainData into dictionary
lastRowMain = mainSheet.Cells(mainSheet.Rows.Count, "A").End(xlUp).Row
For Each cell In mainSheet.Range("A2:A" & lastRowMain)
    mainDict(cell.Value) = cell.Offset(0, 1).Value
Next cell

' Load DetailData into dictionary
lastRowDetail = detailSheet.Cells(detailSheet.Rows.Count, "A").End(xlUp).Row
For Each cell In detailSheet.Range("A11:A" & lastRowDetail)
    detailDict(cell.Value) = cell.Offset(0, 1).Value
Next cell

' Check DetailData against MainData
For Each cell In detailSheet.Range("A11:A" & lastRowDetail)
    If Not mainDict.exists(cell.Value) Then
        ' Highlight missing entries in red with white font (only columns A and B)
        cell.Interior.Color = RGB(255, 0, 0)
        cell.Font.Color = RGB(255, 255, 255)
        cell.Offset(0, 1).Interior.Color = RGB(255, 0, 0)
        cell.Offset(0, 1).Font.Color = RGB(255, 255, 255)
    End If
Next cell

MsgBox "Comparison and highlighting completed."    

I tried to compare two tables in excel