Conditional format last row of positive value in table

[SimonHughes]

Hello, I have a table with 40 rows and depending on input the values change from positive to negative. So with an inflation rate of 2.5% then row 23 will be the first row in negative numbers, if I change inflation rate to 1% then row 28 will be the first row in negative.

How do I conditionally format to highlight the last row in positive numbers? I know how to highlight all negative or all positive, but I would like to isolate the last row in positive territory.

I am using Excel 2016 Win 10

 

[bobsan42]

You didn't provide too much information, but in general:
if the positive and negative values are consecutive in the table and are not intermixed you can use a formula to check if the product of two consecutive cells is not positive (e.g. =$A1*$A2<=0) and apply the desired conditional formatting to the row of A1.

 

[SimonHughes]

You didn't provide too much information, but in general:
if the positive and negative values are consecutive in the table and are not intermixed you can use a formula to check if the product of two consecutive cells is not positive (e.g. =$A1*$A2<=0) and apply the desired conditional formatting to the row of A1.

Thanks Bob, sorry for the lack of detail but you interpreated it fine.

I modified the formula as the two values were in A1 and B1 and as they were both negative values, I added instead of multiplied ending up with =$A1+$B1<=0 and applied the formatting no problem

 

[arthurbr]

Select your range and use the following CF condition =$A1=LOOKUP(9.99999999999999E+307, IF($A$2:$A$18 > 0, $A$2:$A$18)) format as needed and adapt range

( no need to enter as CSE, it is implicit in CF)

 

[SimonHughes]

Thanks arthurb - also a solution