Function ExcelCustomization(resultsPath,Filename,nameSheet, rvalue)
Set ExcelFile = CreateObject(“Excel.Application”)
Set wb = ExcelFile.Workbooks.Open (resultsPath & Filename&”.xls”) ‘To open the existing work book
ExcelFile.Visible=False
wb.Sheets(nameSheet).select ‘To select specific XL sheet
RCols = wb.Sheets(nameSheet).UsedRange.Columns.Count ‘To get the number of columns used
Rrows=wb.Sheets(nameSheet).UsedRange.Rows.Count ‘To get the number of rows used
For cl=1 to RCols
wb. Sheets(nameSheet).Cells(1,cl).Interior.ColorIndex = 53 ‘To set the color to the interior of the XLSheet
wb. Sheets(nameSheet).Cells(1,cl).Font.ColorIndex = 19 ‘To set the color to the fonts of the XLSheet
wb. Sheets(nameSheet).Cells(1,cl).Font.Bold=True
wb. Sheets(nameSheet).Columns(cl).Autofit ‘To set the columns as autofit
wb. Sheets(nameSheet).Cells(1,cl).Borders(1).LineStyle = 1 ‘To Set the Borders
wb. Sheets(nameSheet).Cells(1,cl).Borders(2).LineStyle = 1
wb. Sheets(nameSheet).Cells(1,cl).Borders(3).LineStyle = 1
wb. Sheets(nameSheet).Cells(1,cl).Borders(4).LineStyle = 1
For j=2 to Rrows
rvalue = wb.Sheets(nameSheet).cells(j,cl).Value
If StrComp(rvalue,”PASS”,1)=0 Then
Status=”"
wb. Sheets(nameSheet).Cells(j,cl).Font.ColorIndex=50
wb. Sheets(nameSheet).Cells(j,cl).Font.Bold=True
wb. Sheets(nameSheet).Columns(cl).Autofit
wb. Sheets(nameSheet).Cells(j,cl).Borders(1).LineStyle = 1
wb. Sheets(nameSheet).Cells(j,cl).Borders(2).LineStyle = 1
wb. Sheets(nameSheet).Cells(j,cl).Borders(3).LineStyle = 1
wb. Sheets(nameSheet).Cells(j,cl).Borders(4).LineStyle = 1
ElseIf StrComp(rvalue,”FAIL”,1)=0 Then
Status=”"
wb. Sheets(nameSheet).Cells(j,cl).Font.ColorIndex=3
wb. Sheets(nameSheet).Cells(j,cl).Font.Bold=True
wb. Sheets(nameSheet).Columns(cl).Autofit
wb. Sheets(nameSheet).Cells(j,cl).Borders(1).LineStyle = 1
wb. Sheets(nameSheet).Cells(j,cl).Borders(2).LineStyle = 1
wb. Sheets(nameSheet).Cells(j,cl).Borders(3).LineStyle = 1
wb. Sheets(nameSheet).Cells(j,cl).Borders(4).LineStyle = 1
End If
wb. Sheets(nameSheet).Cells(j,cl).Interior.ColorIndex = 19
wb. Sheets(nameSheet).Columns(cl).Autofit
‘wb. Sheets(nameSheet).Cells(j,cl).Font.Bold=100
wb. Sheets(nameSheet).Cells(j,cl).Borders(1).LineStyle = 1
wb. Sheets(nameSheet).Cells(j,cl).Borders(2).LineStyle = 1
wb. Sheets(nameSheet).Cells(j,cl).Borders(3).LineStyle = 1
wb. Sheets(nameSheet).Cells(j,cl).Borders(4).LineStyle = 1
Next
Next
wb.Sheets(“Con”).select
CCols = wb.Sheets(“Con”).UsedRange.Columns.Count
Crows=wb.Sheets(“Con”).UsedRange.rows.count
For ccl=1 to CCols
wb. Sheets(“Con”).Cells(1,ccl).Interior.ColorIndex = 53
wb. Sheets(“Con”).Cells(1,ccl).Font.ColorIndex = 19
wb. Sheets(“Con”).Cells(1,ccl).Font.Bold=True
wb. Sheets(“Con”).Columns(ccl).Autofit ‘Set Column width
wb. Sheets(“Con”).Cells(1,ccl).Borders(1).LineStyle = 1 ‘To Set the Borders
wb. Sheets(“Con”).Cells(1,ccl).Borders(2).LineStyle = 1
wb. Sheets(“Con”).Cells(1,ccl).Borders(3).LineStyle = 1
wb. Sheets(“Con”).Cells(1,ccl).Borders(4).LineStyle = 1
For crc=2 to crowCount+1
wb. Sheets(“Con”).Cells(crc,ccl).Interior.ColorIndex = 19
wb. Sheets(“Con”).Cells(crc,ccl).Font.Bold=200
wb. Sheets(“Con”).Columns(ccl).Autofit
wb. Sheets(“Con”).Cells(crc,ccl).Borders(1).LineStyle = 1
wb. Sheets(“Con”).Cells(crc,ccl).Borders(2).LineStyle = 1
wb. Sheets(“Con”).Cells(crc,ccl).Borders(3).LineStyle = 1
wb. Sheets(“Con”).Cells(crc,ccl).Borders(4).LineStyle = 1
If StrComp(Environment.Value(“EOT”),”TT”,1)=0 Then
For rc= crowCount+4 to Crows
For cc=1 to 2
wb. Sheets(“Con”).Cells(rc,cc).Interior.ColorIndex = 19
wb. Sheets(“Con”).Cells(rc,cc).Font.ColorIndex = 53
wb. Sheets(“Con”).Cells(rc,cc).Font.Bold=200
wb. Sheets(“Con”).Columns(cc).Autofit
wb. Sheets(“Con”).Cells(rc,cc).Borders(1).LineStyle = 1
wb. Sheets(“Con”).Cells(rc,cc).Borders(2).LineStyle = 1
wb. Sheets(“Con”).Cells(rc,cc).Borders(3).LineStyle = 1
wb. Sheets(“Con”).Cells(rc,cc).Borders(4).LineStyle = 1
Next
Next
End If
Next
Next
wb.Save
ExcelFile.Quit
Set ExcelFile = nothing
Set wb= nothing
Set ExcelSheet = nothing
End If
End Function
