说明:
各个班级花名册excel工作簿,一个余额汇总工作簿,在余额汇总工作簿中有多个表,每个表代表一个班级中每个学生基本信息和余额情况;为了检测班级花名册中的所有学生在余额统计表中是否都有对应的基本信息和余额统计情况,即班级花名册中的所有学生是否在余额统计表中都存在;班级花名册工作簿文件列表:
源代码:
Function CheckAndAddStyle()
Dim sheetToWorkBookName As String
Dim nameIndexInBJ As Integer
For Each sheet In ThisWorkbook.Sheets
sheetToWorkBookName = CStr(2014) + Left(sheet.Name, 2) + ".xls"
Dim myApp As New Application
Dim sh As Worksheet
Dim Temp As String
Temp = ThisWorkbook.Path & "" & sheetToWorkBookName
myApp.Visible = False
Set sh = myApp.Workbooks.Open(Temp).Sheets(1)
nameIndexInBJ = GetNameIndex(sh)
For bjRow = 2 To sh.UsedRange.Rows.Count
Dim yeRow As Integer
For yeRow = 5 To sheet.UsedRange.Rows.Count
If sh.Cells(bjRow, nameIndexInBJ).Value = sheet.Cells(yeRow, 1) Then
sh.Cells(bjRow, nameIndexInBJ).Interior.ColorIndex = 42
Exit For
End If
Next
If yeRow = sheet.UsedRange.Rows.Count + 1 Then
sh.Cells(bjRow, nameIndexInBJ).Interior.ColorIndex = 46
End If
Next
myApp.Quit
Set sh = Nothing
Set myApp = Nothing
Next
End Function
Function GetNameIndex(sheet As Worksheet) As Integer
Dim nameIndex As Integer
For i = 1 To sheet.UsedRange.Columns.Count
If sheet.Cells(1, i).Value = "姓名" Then
nameIndex = i
Exit For
End If
Next
GetNameIndex = nameIndex
End Function