说明:
- 各个班级花名册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) '从sh工作表的第二行开始逐渐检索姓名 'sheet为余额汇总表,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 '设置单元格格式,46是excel颜色代码表示红色 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 'MsgBox CStr(nameIndex) + " " + sheet.Cells(1, i).Value Exit For End If Next GetNameIndex = nameIndex '返回值 End Function