Conditional formating a row until a value is reached

[Eric111]

Excel Gods,

I have been racking my brain for several days trying to figure out this formula. I need to conditionally format a row of cells (C2:V2) to count back from right to left until the value in the target cell (X2) is reached. I would like the row to highlight the cells in the sum range with a red font from right to left. See attached example.

Thanks for your help!!


Example1.xlsx

[Tony Valko]

On the face of it this isn't too difficult but I'm thinking that there are missing details which will make this more complex.

Let's start with this...

Select the range C2:V2
Conditional Formatting
Formula:

=AND(COUNT($X2:$Y2)=2,COLUMNS($C2:$V2)-MATCH(TRUE,SUBTOTAL(9,OFFSET($V2,,,,-(COLUMN($C2:$V2)-COLUMN($C2)+1)))>=$X2*$Y2,0)+1<=COLUMNS($C2:C2))

If you test that formula on the worksheet it must be array entered.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If X*Y is greater than SUM(C2:V2) then no formatting will be applied.

[Eric111]

Thanks Toni,

Your solution works perfectly!

[Tony Valko]

You're welcome. Thanks for the feedback!