Conditional Format to refer to Named Range

[Aquanistic]

Hello everyone!

I have a named range that uses a formula (OFFSET to be specific) to vary the range based on data in my sheet. I would like to point to this named range using Conditional Formatting, but when I enter =Time Left Completing Items V2.3.xlsx'!Mark_Region, and hit Apply, it will change what I entered to the previous range assigned (which was static).

If you have ideas of how to do this, please lay it on me. Thanks!

[protonLeah]

post the formula, and probably a workbook:

To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[Aquanistic]

Right now the user would have to define the visual grid using the format painter, but I would like to have excel automatically apply conditional formatting to the region d1:m15 based on the value of b1.

Here are the formulas defining my named ranges.

Mark_Region_10s:

Please Login or Register  to view this content.

Mark_Region_REM:

Please Login or Register  to view this content.

When I try to choose which cells the conditional formatting refers to to one of these named ranges, it reverts to the previous range, which is static. How can I ask it to refer to one of my named ranges?

Thanks for any way that you can help!

[DBY]

Hello
Not quite sure I understand what you're trying to do with the named ranges but if you need to format the number of cells in the grid of D1:M15 less then or equal to cell B1, then apply the following Conditional Formatting formula to D1:M15:

Please Login or Register  to view this content.

See the Example tab in the attached reply.

Hope this helps.
DBY

[Aquanistic]

Wow, I love your solution. Genius! Thank you so much for helping!

I thought that I had to use named ranges in order to do it. But your solution is far more elegant. Thank you!

I found that using named ranges allowed for graphs to point to conditional ranges so I thought the same would be for conditional formatting.

For the record though, is it indeed possible to point to a named range for a conditional formula?

Thanks again DBY!

[DBY]

Glad to have helped. You can point to named ranges in Conditional formatting but I'm not sure how it could be applied efficiently to the problem you posed. You would still have to apply some sort of count to each cell in the grid in order to compare to cell B1.

Regards
DBY