Sunday, March 29, 2009

QTP Script 34 How to compare two different ranges in a same sheet of excel workbook and highlight the cells with different values in the first range?

I assume here that the count of cells in both the ranges is equal e.g. these two ranges – B6:B38 and F6:F38 have equal number of cells in between them.

myrange=inputbox("enter range of cells e.g. A1:A5")
myrange1=inputbox("enter range of cells e.g. B1:B5")

Excel object is being created here
Set Exl_Obj = CreateObject("Excel.Application")
Exl_Obj.Visible = True

File sac1 is being opened
Set WB_Obj_1= Exl_Obj.Workbooks.Open("C:\sac1.xls")

Set WS_Obj_1= WB_Obj_1.Worksheets(1)

Declaring and setting a range object
set rr1=WB_Obj_1.Worksheets(1).range(myrange)
set rr2=WB_Obj_1.Worksheets(1).range(myrange1)

With rr1
lr1 = .Rows.Count
lc1 = .Columns.Count
End With

With rr2
lr2 = .Rows.Count
lc2 = .Columns.Count
End With

for i= 1 to lr1
for j=1 to lc1
if (rr1.cells(i, j).value)<> (rr2.cells(i, j).value) then
rr1.cells(i, j).Interior.ColorIndex = 6
end if
next
next
Exl_Obj.workbooks("sac1.xls").save
Exl_Obj.workbooks("sac1.xls").close
Exl_Obj.Application.Quit
set Exl_Obj=nothing