数值、字符串的可以了,但是日期型的还是不能充填颜色:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo 100 '遇错略过
Application.ScreenUpdating = False '关闭屏幕更新,加快响应
warng = 5 '设置警报线
Set qy = Worksheets("Sheet1").Cells '设置区域
If Target.Value = "" Then GoTo 100 '空值略过
a = Target.Value + 0 '对输入参数进行类型判断区分
If WorksheetFunction.IsNumber(a) Then
mon = WorksheetFunction.CountIf(qy, Target.Value) '数值型计数
Else
mon = WorksheetFunction.CountIf(qy, a) '字符型计数
End If
If mon >= warng Then '与警报线标准值对照,超过进行处理:
Cells.Select '去掉充填色
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Cells.FormatConditions.Delete '清除条件格式
Cells.ClearComments '清除原有标注
'设置标注内容
Text = "注意:" & Chr(10) & _
Target.Address(0, 0) & "=" & Target.Value & " 出现 " & mon & " 次!"
Target.AddComment '添加标注
Target.Comment.Text Text:=Text '写入内容
'对重复值区域进行充填底色,方便查找:
If WorksheetFunction.IsNumber(a) Then
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=" & a
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Color = 65535
End With
Else
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=" & """" & a & """"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Color = 65535
End With
End If
End If
100:
Cells(Target.Row + 1, Target.Column).Select
Application.ScreenUpdating = True
End Sub
|