How define an Excel range in a conditional format with a range that increases

[TonyK1321]

I have a spreadsheet with 1376 rows that is 6 years of daily data and with 2 columns: 1st=dates, 2nd=value.  I want to do a conditional format that highlights the max value of the values for each year.  I have this formula, and it works, but I want to add to the data set daily, which means I need to adjust the max row number every time I add a row of data.  I would like a formula that adjusts to the size of the column automatically.

Here is my initial condition format that works,  Column A are the dates.  Col E is the data.

=$E2=MAX(IF(YEAR($A2)=YEAR($A$2:$A$1376),$E$2:$E$1376,""))

I could use count(A:A) to get the max row number, but how would I use that in the expression above?  I tried to use the offset but I could not get that to work.  Here is what I tried:

=$E2=MAX(IF(YEAR($A2)=YEAR(offset($A$2,0,0,count(A:A)+1,1)),offset($E$2,0,0,count(A:A)+1,1),""))

The +1 gets me past the title row.

Any help would be appreciated.

[#2]

@TonyK1321 

A suggestion: Would it be possible to turn all data into an actual Excel table? If that is possible, you can give a name for a specific column of data in that table and then refer to that name in the conditional formatting. If the table increases in size, the name will still cover all rows or columns as you have set and consequently your formulation in the conditional formatting will take it into account.

[#3]

@TonyK1321 

As @Martin_Angosto suggested it's better to transform the data into structural table. That's most reliable way.

If to work with the range rule formula as

=$E2=MAX(IF(YEAR($A2)=YEAR(OFFSET($A$2,0,0,COUNTA($A:$A)-1 )),OFFSET($E$2,0,0,COUNTA($A:$A)-1 ),""))

works. Another story how the rule range will be expanded by Excel. It could be not expanded at all, or expanded like

or some other way. Depends on how you add new data and, most probably, on which Excel version you are.

Another way, you may Applies To conditional formatting rule with some gap, e.g. to $A$2:$E$10000, and in formula rule ignore blank rows like

=($E2=MAX(IF(YEAR($A2)=YEAR($A$2:$A$10000),$E$2:$E$10000,"")))*($A2<>"")