|
本帖最后由 0126 于 2019-12-5 18:01 编辑
条件格式
=IF(OR(INDIRECT("rc2",)=INDIRECT("rc3",),COUNTBLANK(INDIRECT("rc2:rc4",))),FALSE,IFERROR(IF(INDIRECT("rc2",)<INDIRECT("rc3",),INDIRECT("rc2",)+INDIRECT("rc3",)=INDIRECT("rc4",),INDIRECT("rc2",)-INDIRECT("rc3",)=INDIRECT("rc4",))=FALSE,TRUE))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
数据有效性
=IF(OR(INDIRECT("rc2",)=INDIRECT("rc3",),COUNTBLANK(INDIRECT("rc2:rc4",))),TRUE,IF(INDIRECT("rc2",)<INDIRECT("rc3",),INDIRECT("rc2",)+INDIRECT("rc3",)=INDIRECT("rc4",),INDIRECT("rc2",)-INDIRECT("rc3",)=INDIRECT("rc4",)))
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
代码
Private Sub Worksheet_Activate() '进入工作表重新全部检查重新设置颜色
On Error Resume Next
Cells.Interior.Pattern = xlNone
For Each rg In ActiveSheet.Range("b2").Resize(ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row)
If Application.WorksheetFunction.CountBlank(rg.Resize(, 3)) = 0 And rg <> rg.Offset(, 1) Then
If IIf(rg < rg.Offset(, 1), rg + rg.Offset(, 1), rg - rg.Offset(, 1)) <> rg.Offset(, 2) Then rg.Resize(, 3).Interior.Color = RGB(255, 0, 0)
End If
Next
End Sub
Private Sub Worksheet_Change(ByVal Target As Range) '修改后检测
On Error Resume Next
Set rg = ActiveSheet.Cells(Target.Row, 2)
rg.Resize(, 3).Interior.Pattern = xlNone
If Application.WorksheetFunction.CountBlank(rg.Resize(, 3)) = 0 And rg <> rg.Offset(, 1) Then
If IIf(rg < rg.Offset(, 1), rg + rg.Offset(, 1), rg - rg.Offset(, 1)) <> rg.Offset(, 2) Then
rg.Resize(, 3).Interior.Color = RGB(255, 0, 0)
' MsgBox Target.Address(0, 0) & "单元格值录入错误"
End If
End If
End Sub
|
|