Conditional Formatting / Positive vs. Negative

Discussion:

(too old to reply)

2007-08-09 14:38:04 UTC

Conditional Formatting

I want to change the font color of a number that is the negative value of
another number. I could conditionally format each cell individually, but that
would be too time-consuming (lots of numbers.)

For example; D5 = 22,443 & D12 = (22,443)

If one cell is the opposite of another cell, change the font color to hide
the negative value.

Any help would be appreciated…
Thx in advance.

Bob Phillips

2007-08-09 14:54:44 UTC

Permalink

Use a custom cell format of

##,##0;[Red](#,##0)
--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Post by JK
Conditional Formatting
I want to change the font color of a number that is the negative value of
another number. I could conditionally format each cell individually, but that
would be too time-consuming (lots of numbers.)
For example; D5 = 22,443 & D12 = (22,443)
If one cell is the opposite of another cell, change the font color to hide
the negative value.
Any help would be appreciated.
Thx in advance.

2007-08-09 15:06:03 UTC

Permalink

That won't work...

Example:
The cell background is dark-grey. If the cell value (in that cell) is the
opposite value of another cell, then I want the font color to be dark-grey -
which would hide the numerical value.

It's not needed unless it's something other than the opposite value of the
other cell.

Thx tho.

Post by Bob Phillips
Use a custom cell format of
##,##0;[Red](#,##0)
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)

Tom Ogilvy

2007-08-09 18:04:01 UTC

Permalink

Maybe something brute force like this:

Sub FormatCell()
Dim cell As Range, cell1 As Range
Selection.Font.ColorIndex = xlAutomatic
For Each cell In Selection
If IsNumeric(cell.Value) Then
If cell.Value < 0 Then
For Each cell1 In Selection
If IsNumeric(cell1) Then
If cell1 > 0 Then
If Abs(cell1 + cell) < 0.0000001 Then
cell.Font.ColorIndex = _
cell.Interior.ColorIndex
Exit For
End If
End If
End If
Next
End If
End If
Next
End Sub
--
Regards,
Tom Ogilvy

Post by JK
That won't work...
The cell background is dark-grey. If the cell value (in that cell) is the
opposite value of another cell, then I want the font color to be dark-grey -
which would hide the numerical value.
It's not needed unless it's something other than the opposite value of the
other cell.
Thx tho.

Post by Bob Phillips
Use a custom cell format of
##,##0;[Red](#,##0)
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)

2007-08-09 20:42:31 UTC

Permalink

I appreciate the effort; although, I'd have to apply this to many-many
different cells in my worksheet. I was hoping for a semi-simple solution
otherwise it's not really worth the effort.

But again, I do appreciate it.

Post by Tom Ogilvy
Sub FormatCell()
Dim cell As Range, cell1 As Range
Selection.Font.ColorIndex = xlAutomatic
For Each cell In Selection
If IsNumeric(cell.Value) Then
If cell.Value < 0 Then
For Each cell1 In Selection
If IsNumeric(cell1) Then
If cell1 > 0 Then
If Abs(cell1 + cell) < 0.0000001 Then
cell.Font.ColorIndex = _
cell.Interior.ColorIndex
Exit For
End If
End If
End If
Next
End If
End If
Next
End Sub
--
Regards,
Tom Ogilvy

Post by Bob Phillips
Use a custom cell format of
##,##0;[Red](#,##0)
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)