Conditional Formatting - Selective Cell Highlighting

[Interloper]

Hi,

I have a column of UKP currency cells (P6 - P34)

Each cell can either be blank (""), zero (0.00), or contain a positive or
negative currency figure.

The cell priority is ascending.

I want to use conditional formatting to highlight the highest number cell
that has either a positive or negative non-zero currency figure in it,
providing there isn't a higher number cell that has zero (0.00) in it.
Blank cells are to be ignored.

Example 1:

cell P6 = 1000.00
cell P7 = 1100.50
cell P15 = 0.00
cell P18 = -340.60
cell P23 = 900.31
All other cells are blank.

Cell P23 would be highlighted because it is the highest number cell with a
non-zero currency figure and there are no higher number cells containing
zero.

Example 2:

cell P6 = 1000.00
cell P7 = 1300.50
cell P12 = 0.00
cell P13 = -270.60
cell P21 = -700.64
All other cells are blank.

Cell P21 would be highlighted because it is the highest number cell with a
non-zero currency figure and there are no higher number cells containing
zero.

Example 3:

cell P6 = 1000.00
cell P7 = 1100.50
cell P12 = 900.31
cell P18 = -220.60
cell P24 = 0.00
All other cells are blank.

No cells would be highlighted because the highest number cell with an entry
is zero.


I though I would be able to sort this one myself, but I'm struggling!

TIA,

 

[isabelle]

hi,

in exemple 3: why the result is not 900.31

Function MaxNoZeroDec(rng As Range) As Double
Dim c As Range, p As String, k As Integer, m As Double
m = -1E+15
For Each c In rng
If c <> 0 And Not (IsError(Application.Find(".", c))) Then
p = Split("" & c.Value, ".")(1)
If Len(p) = 1 Then p = p & "0"
k = Len(p) - Len(Application.Substitute(p, "0", ""))
If k = 0 Then
If c > m Then m = c
End If
End If
Next c
MaxNoZeroDec = m
End Function

--
isabelle



Le 2012-07-24 17:29, Interloper a écrit :

 

[isabelle]

in case the result should be 0

Function MaxNoZeroDec(rng As Range) As Double
Dim c As Range, p As String, k As Integer, m As Double
m = -1E+15
For Each c In rng
If c <> 0 And Not (IsError(Application.Find(".", c))) Then
p = Split("" & c.Value, ".")(1)
If Len(p) = 1 Then p = p & "0"
k = Len(p) - Len(Application.Substitute(p, "0", ""))
If k = 0 Then
If c > m Then m = c
End If
End If
Next c
If m = -1E+15 Then m = 0
MaxNoZeroDec = m
End Function


--
isabelle



Le 2012-07-25 12:48, isabelle a écrit :

 

[Interloper]

Thanks Isabelle, but that seems a lot of code to enter into the Conditional
Formatting 'Formula Is' field.

I've had another look at the problem myself and have resolved it using the
'ISNA' and 'MATCH' functions.

=AND(NOT($P6=""),$P6<>0,AND(ISNA(MATCH(0,$P7:$P$33,1)),ISNA(MATCH(0,$P7:$P$33,-1))))

The above Conditional Formatting formula is entered into cell P6 thro' to
cell P33.

If the cell being evaluated isn't blank, and isn't zero, and no cells in the
column higher than the cell being evaluated have a value of <zero, >zero, or
zero, then the Conditional Formatting will highlight the cell.

I've used a different formula in cell P34 as there are no cells higher than
P34 to be evaluated.

It may not be the most elegant solution, but it works!

What I needed was a function that could look at a column of cells and
determine whether all the cells are blank, or if any cell contained a
positive, negative or zero value. Differentiating between cells that are
truely blank ("") and cells that contain zero (0.00) was the problem.

 

[Claus Busch]

Hi,

Am Sun, 29 Jul 2012 16:10:59 +0100 schrieb Interloper:

What I needed was a function that could look at a column of cells and
determine whether all the cells are blank, or if any cell contained a
positive, negative or zero value. Differentiating between cells that are
truely blank ("") and cells that contain zero (0.00) was the problem.

try in CF:
=P6=INDEX(P634,LOOKUP(2,1/(ABS(P634)>0),ROW(P)))


Regards
Claus Busch

 

[Interloper]

Thanks Claus, I'll give that a try.

Regards,