Conditional Formatting

[SimonHughes]

Formula needed to select the first condition only of the values that meet a given criteria. I want to highlight the first value only that is below 25 (lets assume that 25 is in cell A1 so 26.1 would be in cell A3 and so on). So in this case, I want to format cell A 4 as this is the first value vbelow 25. Just need a TRUE/FALSE formula that I can drop into conditional formatting.

Ia m using Excel 2010 on WIn 7

<TABLE style="WIDTH: 93pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=124><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=124>25</TD></TR><TR style="HEIGHT: 60pt" height=80><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 60pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=80 width=124>PM2.5 Penetration</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>26.1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>19.7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>10.7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>6.1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>3.2</TD></TR></TBODY></TABLE>

 

[AlphaFrog]

=COUNTIF($A$3:A3, "<"&$A$1)=1

This would be if cell A3 was the top cell in the list of values to check. The 2nd A3 in the formula will automatically change for cells A4, A5, A6...etc.


EDIT: Use this if your list would have some blank cells.

=AND(COUNTIF($A$3:A3, "<"&$A$1)=1, A3<>"")

 

[SimonHughes]

That's excellent, many thanks for your help.