Hi Need help to Highlight mismatched rows(compare 2-3 , 4-5,6-7 rows so on dynamically) data Using SAS while generating a multi sheet excel file . SAS is on Unix server. I am using SAS EG 7.1 Below is the data I have to compare every two rows and highlight the mismatched data while generating the Excel and send it via email. Note - there are multiple sheets and columns vary (sometimes there maybe 10 columns, sometimes it may be 18) I have SAS code(used ODS) to generate excel with multiple sheets and send it via email and separate a VBA macro to highlight the mismatched data which I run manually. But I am unable to integrate both in to single code so that no manual work is needed, I really appreciate if anyone can suggest any way to do it in one single code. Below is sample dataset , rows and column are dynamically created based on the project. Need to compare 2-3,4-5, 6-7..so-on rows and highlight(I have given blue to the font but i need whole box as yellow) only when they don’t match. ssn firstname lastname empid address city state zip 000-11-1234 abc d 1001 plotno 123 abcd rd 12345 000-11-1234 abc e 1001 plotno 123 abcd rd 12345 111-12-3456 efgh f 2001 house 897 tyui fg 23456-789 111-12-3456 efgh f 2001 hs 897 tyui fg 23456 444-56-7890 wrt y 3001 xc 12345 444-56-7890 wert y 3002 xc 12345 my VBA code: Sub compare_Auto() Dim LastColumn As Long LastColumn = ActiveSheet.Range("A1").CurrentRegion.Columns.Count Dim rng As Range, rngDiff As Range Set rng = Range(Cells(2, 7), Cells(3, LastColumn)) Do While Application.CountA(rng) > 0 'loop while have content Set rngDiff = Nothing 'reset range On Error Resume Next 'ignore "No cells were found" error if no differences Set rngDiff = rng.ColumnDifferences(Comparison:=rng.Cells(1)) On Error GoTo 0 'stop ignoring errors If Not rngDiff Is Nothing Then rngDiff.Interior.ColorIndex = 6 'color all differences rngDiff.Offset(-1).Interior.ColorIndex = 6 End If Set rng = rng.Offset(2) 'two rows down Loop End Sub
... View more