Which ranges are you checking? You just need to loop and check the CF formula directly rather than trying to tweak the formula string in code.
Rory
Private Sub worksheet_deactivate()
Dim varReturn
varReturn = Evaluate("IF(AND(AND(OR(((F8-I8)/I8>0.2),((F8-I8)/I8<-0.2)),NOT(ISBLANK(I8)),NOT(ISBLANK(F8))),AND(NOT(ISBLANK(F8)),NOT(ISBLANK(G8)),NOT(ISBLANK(H8)),NOT(SUM(G8+H8)=F8))),TRUE,FALSE)")
If Not IsError(varreturn) Then
If varReturn Then MsgBox "Test", vbOKCancel
End If
End Sub
Private Sub worksheet_deactivate()
Dim varTotalResult
Dim StartRow
Dim EndRow
Dim varReturn
Dim t
StartRow=8
EndRow=18
varTotalResult=False
For t=StartRow to EndRow
varReturn = Evaluate("IF(AND(AND(OR(((F" & Trim(t) & "-I" & Trim(t) & ")/I" & Trim(t) & ">0.2),((F" & Trim(t) & "-I" & Trim(t) & ")/I" & Trim(t) & "<-0.2)),NOT(ISBLANK(I" & Trim(t) & ")),NOT(ISBLANK(F" & Trim(t) & "))),AND(NOT(ISBLANK(F" & Trim(t) & ")),NOT(ISBLANK(G" & Trim(t) & ")),NOT(ISBLANK(H" & Trim(t) & ")),NOT(SUM(G" & Trim(t) & "+H" & Trim(t) & ")=F" & Trim(t) & "))),TRUE,FALSE)")
If Not IsError(varreturn) Then
If varReturn Then varTotalResult=true
End If
Next
'Final Message
If varTotalResult Then MsgBox "Test", vbOKCancel
End Sub
Private Sub worksheet_deactivate()
Dim varReturn
Dim rngCell As Range
' change range as needed
For Each rngCell In Range("A1:A10")
varReturn = Evaluate(Mid$(rngCell.FormatConditions(1).Formula1, 2))
If Not IsError(varReturn) Then
If varReturn Then
MsgBox "Test", vbOKCancel
' assumes you want to exit if any of them are true
Exit Sub
End If
Next rngCell
End Sub
For Each rngCell In Range("A1:A10")
Private Sub worksheet_deactivate()
Dim varReturn
Dim rngCell As Range
' change range as needed
For Each rngCell In Range("A1:A10")
varReturn = Evaluate(Mid$(rngCell.FormatConditions(1).Formula1, 2))
If Not IsError(varReturn) Then
If varReturn Then
MsgBox "Test", vbOKCancel
' assumes you want to exit if any of them are true
Exit Sub
end if
End If
Next rngCell
End Sub
Private Sub worksheet_deactivate()
Dim varReturn
Dim rngCell As Range
' change range as needed
For Each rngCell In Range("F8:F15")
varReturn = Evaluate(Mid$(rngCell.FormatConditions(1).Formula1, 2))
If Not IsError(varReturn) Then
If varReturn Then
MsgBox "Test", vbOKCancel
' assumes you want to exit if any of them are true
Exit Sub
end if
End If
Next rngCell
End Sub
Private Sub worksheet_deactivate()
Dim varReturn
Dim rngCell As Range
Dim FormatCond As FormatCondition
Dim intErr As Integer
On Error GoTo ResetError
intErr = 0
' change range as needed
For Each rngCell In Range("F8:F14")
For Each FormatCond In rngCell.FormatConditions
varReturn = Evaluate(Replace(Mid$(FormatCond.Formula1, 2), ";", ","))
If intErr = 0 Then
If Not IsError(varReturn) Then
If varReturn Then
MsgBox "Test", vbOKCancel
' assumes you want to exit if any of them are true
Exit Sub
End If
End If
Else
intErr = 0
End If
Next
Next rngCell
Exit Sub
ResetError:
intErr = Err.Number
Resume Next
End Sub
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
8 Experts available now in Live!