Sub ReadCellContent()

    Dim ColHeader As Range
    Set ColHeader = Range("A1", Range("A1").End(xlToRight))
    Dim ColData As Range
    
    For Each ColData In ColHeader
        If ColData.Offset(1, 0) = "" Then
            ColData.Interior.Color = vbYellow
        End If
       
        If ColData.Offset(1, 0) <> "" Then
            Dim ColRange As Range
            Dim lData As Range
            Set lData = Cells(Rows.Count, ColData.Column).End(xlUp) '.Row
            Set ColRange = Range(ColData.Offset(1, 0), lData)
            x = ColRange.Rows.Count
            y = WorksheetFunction.CountIf(ColRange, ColData.Offset(1, 0))
                If x = y Then
                 ColData.Interior.Color = vbGreen
                 Else
                 ColData.Interior.Color = vbRed
                End If
        End If
          
    Next ColData
End Sub