Conditional formatting dynamic named range

[babycody]

I am using this formula for conditional formating =MOD(ROW()-2,1*2)+1<=1 to get stripes. On this same sheet I have a dynamic named range. Is there any way to write the formula so that it formats the dynamic named range, and grows as the range grows? Thanks everyone for any help you can give me.

 

[GorD]

The only way I can think of doing it, is to incorporate the same criteria that you use to create the dynamic range into your conditional format with an AND function.

Assuming you are using offset and countA to create your dynamic range, then there is a column that must be non blank for the format to kick in - use that same criteria in the conditional format.

You will have to apply the format to all the cells that may at sometime be included in the range however.

 

[Peter_SSs]

babycody

Not sure about extending to named ranges but I think
=MOD(ROW()+1,2)
is a shorter formula that would get you the same stripe result in your conditional formatting. And if you didn't mind have the stripes on the alternate rows to what you have now
=MOD(ROW(),2)
would do it.

 

[babycody]

I am using this formula structure for my dynamic named range =OFFSET('SUMMARY SHEET'!$A$2,0,0,COUNTA('SUMMARY SHEET'!$A:$A),1)

 

[barry houdini]

So your dynamic range is in column A?

presumably you have text entries without gaps and the range expands automatically if you add extra text entries to it. If so then....

You can probably do something as simple as selecting column A and using Conditional Formatting

Formula is

=ISTEXT(A1)

If you want the formatting of your dynamic range to override your alternate row formatting then make this condition 1 and your alternate rows condition 2

 

[GorD]

=AND($A12<>"",MOD(ROW()-2,1*2)+1<=1)

copied down and across should work. But will need copied to all cells which may at sometime get a entry, prior to that entry

Barry - why do you conclude that column A only contains text enteries?

 

[barry houdini]

Barry - why do you conclude that column A only contains text enteries?

Clearly my brain's not working tonight GorD

you're right I should have gone for A1<>""

 

[babycody]

I guess barry got lucky because it was text. The most helpful suggestion he made was about the order of the conditions. I had forgotten about the conditions having dominance over each other. That helped a lot. I used =MOD(ROW()+1,2)+1<=1 as the first condition with a fill color of white. The second condition was =ISTEXT($A2) with a different color. The only thing I don't like is that the first condition still fills ever other row with white all the way down. You can't see it, but I would prefer more control over the first condition since this method could interfere with formating a sheet's cells. However it does looks really nice now.

 

[GorD]

So does

=AND($A2<>"",MOD(ROW()-2,1*2)+1<=1)


not do what you need, as one condition. (copied down and across)

 

[babycody]

So does

=AND($A2<>"",MOD(ROW()-2,1*2)+1<=1)


not do what you need, as one condition. (copied down and across)

Thanks GorD that is much better. Now I will look into how the AND function works. Never tried using it before, but this peaked my interest. I want to thank everyone for their many posts to help me on my problem. It is much appreciated.

On a side note I used =AND($A2<>"",MOD(ROW(),2)) as a second condition. This allowed my to include borders around the nonfilled cells. Otherwise the bottom might not have a border.