Data Validation & Quality Check

Sub DataQualityCheck()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim blankCount As Long, errorCount As Long
    Dim totalCells As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    totalCells = lastRow * lastCol

    ' Check for blanks and errors
    For i = 1 To lastRow
        For j = 1 To lastCol
            If IsEmpty(ws.Cells(i, j)) Then
                blankCount = blankCount + 1
            ElseIf IsError(ws.Cells(i, j)) Then
                errorCount = errorCount + 1
                ws.Cells(i, j).Interior.Color = RGB(255, 0, 0) ' Highlight errors in red
            End If
        Next j
    Next i

    ' Create quality report
    ws.Range("A" & lastRow + 3).Value = "Data Quality Report"
    ws.Range("A" & lastRow + 4).Value = "Total Cells: " & totalCells
    ws.Range("A" & lastRow + 5).Value = "Blank Cells: " & blankCount & " (" & Round((blankCount / totalCells) * 100, 2) & "%)"
    ws.Range("A" & lastRow + 6).Value = "Error Cells: " & errorCount & " (" & Round((errorCount / totalCells) * 100, 2) & "%)"
    ws.Range("A" & lastRow + 7).Value = "Data Completeness: " & Round(((totalCells - blankCount) / totalCells) * 100, 2) & "%"

    MsgBox "Data quality check complete! Found " & blankCount & " blanks and " & errorCount & " errors."
End Sub