我正在为我的组织制作一张rota表,我需要VBA代码,根据员工的假期计数来突出显示单元格。
如果超过5名员工在同一日期(列)申请休假,则日期列应以红色突出显示。
有人可以建议相同的VBA代码吗?
附上示例屏幕截图
P - 员工在场V - 员工在度假
发布于 2018-09-10 10:03:56
你很幸运我今天真的很无聊:p
这是最有效的方法吗?绝对不是,但对于我的测试(假设每个工作表只保存一个月的数据),它工作得很好。另请注意,这仅适用于UPPERCASE V
来表示休假
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("B2:V11")
If Not Intersect(Target, rng) Is Nothing Then
'scan each row (month)
Dim countRow As Long
Dim i As Long
For i = 1 To rng.Rows.count
If Not Intersect(Target, rng.Rows(i)) Is Nothing Then
If WorksheetFunction.CountIf(rng.Rows(i), "V") > 0 Then
countRow = 0
Dim cel As Range
For Each cel In rng.Rows(i).Cells
If cel.Value2 = "V" Then
countRow = countRow + 1
VacationChange cel, countRow
Else
VacationChange cel, 0
End If
Next cel
End If
End If
Next i
'scan each column (day)
Dim j As Long
For j = 1 To rng.Columns.count
If Not Intersect(Target, rng.Columns(j)) Is Nothing Then
If WorksheetFunction.CountIf(rng.Columns(j), "V") > 5 Then
VacationChange rng.Columns(j).Cells(0, 1), 6
Else
VacationChange rng.Columns(j).Cells(0, 1), 0
End If
End If
Next j
End If
End Sub
Private Function VacationChange(ByVal rng As Range, ByVal count As Long)
With rng.Interior
Select Case count
Case 0
'clear cell colors
.Pattern = xlNone
.Color = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
Case 1 To 3
'blue
.Pattern = xlSolid
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
Case 4 To 5
'yellow
.Pattern = xlSolid
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
Case Else
'red
.Pattern = xlSolid
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End Select
End With
End Function
证明:
https://stackoverflow.com/questions/-100002589
复制相似问题