H
hotplate
Is it possible to conditionally format a cell that has a value of #DIV/
0?
0?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Rick Rothstein
I don't think so. You can signal any error condition by using ISERROR(A1),
or just ISERR(A1) if you don't care about #NA errors, as your Conditional
Formatting formula, but I don't think you can narrow it down to just the
#DIV/0! error. I would think you should be able to use Event code to mimic
the behavior of Conditional Formatting if a VB solution is acceptable.
or just ISERR(A1) if you don't care about #NA errors, as your Conditional
Formatting formula, but I don't think you can narrow it down to just the
#DIV/0! error. I would think you should be able to use Event code to mimic
the behavior of Conditional Formatting if a VB solution is acceptable.
R
Rick Rothstein
The following event code should do what I said (mimic Conditional Formatting
for #DIV/0! errors only). Note that, as written, it looks for these errors
in Columns A and B only (this can be changed to another column or an
arbitrary range if needed by changing the address assigned to the Addr
constant in the code); and the code assumes there are formulas in Column B
(that is, the assumption is that your user didn't simply type =2/0 into a
cell in Column B, but rather, a formula in that column evaluated to the
error).
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Const Addr As String = "A:B"
On Error GoTo SkipIt
For Each R In Target.Dependents
If Not Intersect(Range(Addr), R) Is Nothing Then
R.Interior.ColorIndex = xlNone
If IsError(R.Value) Then
If R.Value = CVErr(2007) Then R.Interior.ColorIndex = 3
End If
End If
Next
SkipIt:
End Sub
You would install this event code by right-clicking the tab at the bottom of
the worksheet you want to have this functionality, selecting View Code from
the popup menu that appears and then copy/pasting the above code into the
code window that opened up.
for #DIV/0! errors only). Note that, as written, it looks for these errors
in Columns A and B only (this can be changed to another column or an
arbitrary range if needed by changing the address assigned to the Addr
constant in the code); and the code assumes there are formulas in Column B
(that is, the assumption is that your user didn't simply type =2/0 into a
cell in Column B, but rather, a formula in that column evaluated to the
error).
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Const Addr As String = "A:B"
On Error GoTo SkipIt
For Each R In Target.Dependents
If Not Intersect(Range(Addr), R) Is Nothing Then
R.Interior.ColorIndex = xlNone
If IsError(R.Value) Then
If R.Value = CVErr(2007) Then R.Interior.ColorIndex = 3
End If
End If
Next
SkipIt:
End Sub
You would install this event code by right-clicking the tab at the bottom of
the worksheet you want to have this functionality, selecting View Code from
the popup menu that appears and then copy/pasting the above code into the
code window that opened up.
E
EricG
Don't know if this works in 2003, but in 2007 you can use this as your
formula (assumes the #DIV is in A1):
=If(ERROR.TYPE(A1)=2,1,0)=1
Then set the fill to red or whatever conditional format you want!
From Excel Help:
If error_val is ERROR.TYPE returns
#NULL! 1
#DIV/0! 2
#VALUE! 3
#REF! 4
#NAME? 5
#NUM! 6
#N/A 7
Anything else #N/A
HTH,
Eric
formula (assumes the #DIV is in A1):
=If(ERROR.TYPE(A1)=2,1,0)=1
Then set the fill to red or whatever conditional format you want!
From Excel Help:
If error_val is ERROR.TYPE returns
#NULL! 1
#DIV/0! 2
#VALUE! 3
#REF! 4
#NAME? 5
#NUM! 6
#N/A 7
Anything else #N/A
HTH,
Eric
R
Rick Rothstein
Yes, very good... that works fine in XL2003. For some reason ERROR.TYPE just
didn't come to mind when I read this question initially. Thanks for jumping
in with that... it should be exactly what the OP wants.
didn't come to mind when I read this question initially. Thanks for jumping
in with that... it should be exactly what the OP wants.