CODEWITHSUNDEEP
excel
Dominik Antalik
Dominik Antalik

Reputation: 53

Conditional formatting - first value lower than

I need to highlight only first number in sorted order.

For example I have a row:

10000    8250    6500    4750    3000    1250

All I need is to highlight first cell which is lower than 5000. So value 4750 will be "green" but other two cells after that cell will be again without formatting.

Upvotes: 4

Views: 504

Answers (3)

zipa
zipa

Reputation: 27869

Very interesting question.

Here is my idea for range B1:H1 (duplicate values resolved):

10000   8250    6500    4750    4750    3000    1250
10000   8250    6500    5000    5000    3000    1250
10000   8250    6500    4750    4750    3000    1250

Now, in cell J1 you should go with formula:

=INDIRECT(CHAR(64+MATCH(4999,B1:H1,-1)+2)&ROW(H1))

This formula will give you the first value lower than 5000.

Now, the custom formula for formatting would be:

=IF(AND(B1=$J1,B1<A1),1,0)

This way you would avoid duplicate Green values, and it explains why I didn't start with column A.

Upvotes: 0

Dominik Antalik
Dominik Antalik

Reputation: 53

I try some "magic formula" which is working in my scenario, although it isn't universal solution. In case, somebody had similiar problem as me, you can use and edit this formula. Btw, I had to write in this formula for every cell seperate, not for range

=AND(INDIRECT("RC[+1]";0)F8;ISBLANK(INDIRECT("RC[-1]";0)));NOT(INDIRECT("RC[-1]";0)INDIRECT("RC[+2]";0))))

Upvotes: 0

Tom Malkin
Tom Malkin

Reputation: 2284

Cool question because the formula has to take into account the first value. My solution is using conditional formatting with the following formula:

=COUNTIFS($G25:G25,"<5000")=1

Where row 25:25 is the row with the values, with the first value being in G25. Keeping the first G absolute referenced keeps the COUNTIF range growing with each new cell in the conditional format.

Upvotes: 1

Related Questions