Apply Conditional Format to a Named Range

[detriez]

I need to highlight BLANK cells in file but the row count will vary file to file so I've created a Dynamic Named range

Im trying to apply this Conditional Formatting formula to a Named Range nRequired

This formula woks when i declare the cells but I cant figure out how to apply it to the dynamic named range of cells
I tried adding =nRange and the nRange formula to "Applies to:", but neither worked

=AND(COUNTA($D1:$M1)>0,D1="")

 

[6StringJazzer]

You cannot use a named range to define the "Applies To" range for conditional formatting. But in your case you don't need to use the named range.

Since the range will vary only by the number of rows, you can just apply it to all relevant columns. This should be fine unless you are using an array formula, which you are not.

Your formula will highlight a cell if the cell is blank AND that row has any nonblank cells. This formula will work how you want it to with an Applies To range of $D:$M.

 

[detriez]

Thanks @6StringJazzer
Yup... Your right.. That works as expected

Here is where the named range comes in. The result of this file is an import into another system. If the user does not delete everything after the last row, it will create empty records. Our users tend to get sloppy and forget this step.

If I add the conditional formatting up to row 200 and there are only 25 records on the file, it will cause a problem.

i'm not convinced Dynamic Named Range is the answer here but, I din't know of another way to do this

 

[6StringJazzer]

Maybe I wasn't clear. You don't add conditional formatting up to row 200, or up to any other row. You apply it to the entire columns. Excel will then automatically apply it to all rows in the used range of the worksheet. Adding conditional formatting will not add empty records, so it will not affect your export to the other system. The used range can be seen when you hit CTRL+END and it will go to the lower right cell in the used range.

Maybe I'm not understanding the issue you mentioned with how users get sloppy, but I believe it is unrelated to conditional formatting.

If there is still any question about this I can provide a simple sample file.