Dynamic Conditional Formatting for Range without a Macro

[snuffnchess]

I am currently using the following formula to apply conditional formatting to rows in a dataset

=MOD(ROW(),2)=1

And having it apply to

=$A$2:$H$203

Row 203 is currently the last row of data - but I want to make it so that if somebody adds/removes a row that the formatting applies to to that last row of data.

I Have tried creating a named rng that would calculate the last row of data - but when i click "apply" in conditional formatting, it just updates to a straight formula and removes the formula and saves it as a fixed range.
Tried stuff like

=$A$2:INDEX($G:$G, MLR)

where MLR =

=COUNTA(Monthly!$A:$A)

I even tried creating a named range for the entire table (called mrng) with

=OFFSET(Monthly!$A$2,0,0,COUNTA(Monthly!$A:$A),8)

, but every time i click "apply" it just goes back to a fixed range.

How does one do this??

 

[ExcelToDAX]

What is the table name? For example, if your table is named Table1, you could use:

=Table1[#All]

for your named range

 

[snuffnchess]

What is the table name? For example, if your table is named Table1, you could use:

=Table1[#All]

for your named range

I do not have this set as a "table" because I did not want table formatting to apply to it. That was part of my thinking in creating the named range that was more dynamic in nature.

 

[Joe4]

I do not have this set as a "table" because I did not want table formatting to apply to it

That is kind of interesting, because from the picture in your original post, it looks like this Conditional Formatting (and maybe the use of Filters) appear to try to make it look like Table formatting.
So, is it you want it to look like a table, but not really be a table?
Can I ask why? It just seems a little odd, and if we have an understanding of why you are trying to do it this way, we can offer some better suggestions.

If you had at least one extra row after the end of your data included in the original Conditional Formatting range, then if you insert or delete any rows BEFORE that last formatted row, your Conditional Formatting range will grow/shrink accordingly without you having to do anything special. Of course, that could be hard to enforce, if you are not the only one using it.

Alternatively, you could use VBA to automatically update the Conditional Formatting Rules upon data entry.

 

[snuffnchess]

That is kind of interesting, because from the picture in your original post, it looks like this Conditional Formatting (and maybe the use of Filters) appear to try to make it look like Table formatting.
So, is it you want it to look like a table, but not really be a table?
Can I ask why? It just seems a little odd, and if we have an understanding of why you are trying to do it this way, we can offer some better suggestions.

If you had at least one extra row after the end of your data included in the original Conditional Formatting range, then if you insert or delete any rows BEFORE that last formatted row, your Conditional Formatting range will grow/shrink accordingly without you having to do anything special. Of course, that could be hard to enforce, if you are not the only one using it.

Alternatively, you could use VBA to automatically update the Conditional Formatting Rules upon data entry.

That extra line at the end may be a good option... so thank you for that idea. Honestly, the reason I avoided the table route is just from a straight formatting issue. I do not like the default table colors, and did not want to have to recreate for each workbook.

 

[Joe4]

You are welcome.

Note that you can change the default colors of tables, in case you are interested in looking at that.
See here: Formatted Table – Change Default Colour.