Colour the first cell in a row/range cell where a value exceeds $x

Hi,

I have a range of cells (A1:A12) each with monthly sales figures.

I need to highlight where in the range the values exceed a certain amount ($50k for example). However it's only the first cell that hits the value that should be coloured, i.e. once it reaches the figure nothing afterwards is to be coloured. So it could occur in cell A3 nothing afterward should be coloured.

I've tried using conditional formatting but I can't get the cell to determine if any other cell is coloured.

Any ideas would be appreciated and I've attached a sample file.

Thanks

David

=AND(SUM($A$2:B2)>=$O2,SUM($A$2:A2)<$O2) as the conditional formatting for cell B2 (then copy, paste special, formats to other columns), with the threshold for that row is in column O (you could replace the $O2 with 50000, but the formula allows for dragging down with different thresholds per row). You may need to adjust it if the threshold is reached in Jan.

Hi Gak267,

Thanks for getting back to me.

I've tried the formula (=AND(SUM($A$2:B2)>=$O2,SUM($A$2:A2)<$O2)) in B2 with the value of $2000 in O2, but the formatting doesn't show.

Could you attached a sample file for me to have a look at?

Thanks

David

Coloured as Exceeds $X (1).xlsx
As requested. A2 has a different conditional formatting equation.

Originally Posted by DaveBre

I've tried the formula (=AND(SUM($A$2:B2)>=$O2,SUM($A$2:A2)<$O2)) in B2 with the value of $2000 in O2, but the formatting doesn't show

As I said:

You may need to adjust it if the threshold is reached in Jan

I have done that in the file uploaded.

Hi Gak67,

That's for the posts, I've managed to get it working across my dataset.

Many thanks

David