easily apply conditional format formula to all rows

i have the following formula applied to a conditional format, on row 12:

cell value > less than =($K12:Y$111)*(1-0.05)

i want to use this on all rows, however i have 300+ rows.

Is there a way to apply this to all rows between column K and Y, i don't like the thought of creating 300+ conditional formats!

the format is supposed to be looking at a series of values and highlighting ones that are 5% lower than highest number in the row (which is in another column using =MAX(L12:Y12))

i've applied it to one row, and want to use it on all lines.

@royUK i don't understand, copy and paste where?

i've applied it to one row, and want to use it on all lines.

@royUK i don't understand, copy and paste where?

Copy the source row, then select the target rows for which you want to apply the same CF, and do a either a Paste (if the cells are empty) or Paste Special, Formats (if there are data/formula in the cells).

Is the max formula in column K?

Try selecting the whole range you want to format first, e.g. L12:Y400 [you can select that range easily by typing L12:Y400 into the box above A1 and presssing ENTER]

Now apply conditional formatting by using "formula is" and this formula

=(L12<$K12*0.95)*(L12<>"")

format as required > OK

Originally Posted by daddylonglegs

Is the max formula in column K?

Try selecting the whole range you want to format first, e.g. L12:Y400 [you can select that range easily by typing L12:Y400 into the box above A1 and presssing ENTER]

Now apply conditional formatting by using "formula is" and this formula

=(L12<$K12*0.95)*(L12<>"")

format as required > OK

maybe i havent followed this correctly, but its highlighting random cells, including this highest value (which is obviously not 5% lower than the highest!)

Try checking the conditional formatting formula in L12. If you applied it correctly it should be as follows:

=(L12<$K12*0.95)*(L12<>"")

here's an example.....values are randomly generated, press F9 to generate again....

Still not getting the right results, i've attached a sample of my workbook, maybe you can see the problem.

i've highlighted some of the rows that are returning incorrect results.

yeah that's got it, all because i had a formula for row 12 and applied it from row 8..... *Sigh*

thanks for all the help

Using your posted workbook I selected the range L8:R88 (obviously you can extend the range rightwards if you wish) then used this slightly modified formula

=(L8<$K8*0.95)*(L8<>"")*(M8="")

thanks, i guess i was over thinking it possibly.

I will put this into action in the morning and let you know how it goes.


thanks again

yeah, that works great.

thanks