If there's a value in the specified range, highlight row

Question

I'm writing an application to copy values from a Word table to Excel. Every time a new table is found it will put those values in a new column. Most tables are identical, some aren't. I want a rule to highlight rows which have a value anywhere in the range AQ2 to JK1986.

I can highlight a row if a specific column has a value. Currently for a single column I use:

   =NOT($AQ2="")  

but as you can imagine I don't want 250 such rules. Just removing the $ sign doesn't do anything. And making a formula stating $AQ2:$JK2 will always just give a true. I can also use the "Format only cells" to highlight the values but not the whole row, and I need it to highlight the whole row so I can see which documents have aberrant tables.

Is there a faster way to create a rule to highlight rows from AQ2 to JK1986 which have a value?

Answer 1

To highlight every row in the range 2:1986 where any cell in columns AQ to JK inclusive in that row is populated, use a Conditional Formatting formula rule of:

 =COUNTA($AQ2:$JK2)>0  

with highlighting of your choice and the Applies to range set to:

=$2:$1986