Is it possible to conditionally format a cell that has a value of #DIV/
0?

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.

--
Rick (MVP - Excel)



"hotplate" wrote in message
...
Is it possible to conditionally format a cell that has a value of #DIV/
0?

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.

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)



"hotplate" wrote in message
...
Is it possible to conditionally format a cell that has a value of #DIV/
0?

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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)



"hotplate" wrote in message
...
Is it possible to conditionally format a cell that has a value of #DIV/
0?

.

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.

--
Rick (MVP - Excel)



"EricG" wrote in message
...
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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)



"hotplate" wrote in message
...
Is it possible to conditionally format a cell that has a value of
#DIV/
0?

.