PDA

View Full Version : how to Avoid zero in conditional formatting?



rrosa1
02-27-2011, 05:00 PM
hi i have following code to add cond.formating but in column there is "0" in cell
so it formet that cell but i need to find small num. in column not the zero
is there any way i can do that?
thanks for looking my requestcl
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(RC=SMALL(R4C:R367C,1),RC=SMALL(R4C:R367C,2))" For Each cl In rng
Select Case cl.Value
Case Is = 0
Case Is = ""
Case Else
With rng.FormatConditions(2)
With .Font
.Bold = True
.ColorIndex = 5 '3
End With
'.Interior.ColorIndex = 39
End With
End Select
Next

Bob Phillips
02-28-2011, 12:58 AM
Untested



"=AND(RC<>0,OR(RC=SMALL(R4C:R367C,1),RC=SMALL(R4C:R367C,2)))"

Bob Phillips
02-28-2011, 01:06 AM
If you mean the 0 is in the list of numbers, try



=OR(L1=SMALL(IF(L$4:L$367>0,L$4:L$367),{1,2}))

rrosa1
02-28-2011, 07:22 AM
hi xld
thanks for that

"=AND(RC<>0,OR(RC=SMALL(R4C:R367C,1),RC=SMALL(R4C:R367C,2)))"
but this work ,but it don't find the small num.

and the next one give the error

Run time error '5'
" Invalid procedure call or argument"

"=OR(RC=SMALL(IF(R4C:R367C>0,R4C:R367C),{1,2}))"

any insight in this is appreciated.

Bob Phillips
02-28-2011, 08:44 AM
Can you post the workbook?

rrosa1
02-28-2011, 08:56 AM
hi
xld
hear is the wb sample

Bob Phillips
02-28-2011, 09:12 AM
Where is the code in here?

rrosa1
02-28-2011, 09:42 AM
there is module in wb u can run for conditional formating