named ranges and conditional formatting combination

[littlepete]

hello

in my extended address list, i use a lot of named ranges. I also use a lot of conditional formatting to highlight data in the near future.
i do check around all my data regularly, but i always discover the same two problems:

both the list of named ranges as the list of cond. formatting get multiplied each time rows are added...

i have been searching so much to find a way to solve this reproducing of rules, but so far i didn't find anything...

to be clear:
cond.form. for a5:a400 changes after adding row 200 into: a5:a199 - a200 - a201-a400...

is there a way to solve this?

question about this: someone longtime ago advised me to change my list into a tabel but i'm afraid i will lose functionality when i do that?
is that a solution?

have a nice day !

 

[arthurbr]

question about this: someone longtime ago advised me to change my list into a tabel but i'm afraid i will lose functionality when i do that?
is that a solution?

Yes, this is the way to go. Try it after backuping your data, it's really worthwhile
I don't see which functionality you risk loosing

 

[littlepete]

Yes, this is the way to go. Try it after backuping your data, it's really worthwhile
I don't see which functionality you risk loosing

hello,


i did try transforming the data to a table.
macros are not working; function keys are not working; autofilters are not working.

i guess i'm supposed to only watch my list?

 

[littlepete]

as a solution to not have this problem,
i'm writing vba to apply all conditional formatting...

but as always it does not work as i expect...
the conditional formatting colors rows with data on today; tomorrow; day+2 and day +3; yesterday; day -2 and day -3.

it should color the whole row (columns a to f) when in column Q or column Y or column AM the date is today, or yesterday or ...

it does apply the first vba rule i can see the result in the list of cond. formatting,
and: the other lines are in there too, but without the coloring...

this is the code:

Sub voorwopm()

'Define Range
Dim alledata As Range
Dim gegevens As Worksheet

Set gegevens = ActiveSheet
gegevens.Cells.FormatConditions.Delete

Set alledata = gegevens.Range("A5:f381")
    alledata.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=OF(EN(DAG($Q5)=DAG($A$3);MAAND($Q5)=MAAND($A$3));EN(DAG($Y5)=DAG($A$3);MAAND($Y5)=MAAND($A$3));EN(DAG($AM5)=DAG($A$3);MAAND($AM5)=MAAND($A$3)))"
        alledata.FormatConditions(1).Interior.ColorIndex = 5
    alledata.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=OF(EN(DAG($Q5)=DAG($A$3)+1;MAAND($Q5)=MAAND($A$3));EN(DAG($Y5)=DAG($A$3)+1;MAAND($Y5)=MAAND($A$3));EN(DAG($AM5)=DAG($A$3)+1;MAAND($AM5)=MAAND($A$3)))"
        alledata.FormatConditions(1).Interior.ColorIndex = 41
    alledata.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=OF(EN(DAG($Q5)=DAG($A$3)+2;MAAND($Q5)=MAAND($A$3));EN(DAG($Y5)=DAG($A$3)+2;MAAND($Y5)=MAAND($A$3));EN(DAG($AM5)=DAG($A$3)+2;MAAND($AM5)=MAAND($A$3)))"
        alledata.FormatConditions(1).Interior.ColorIndex = 33
    alledata.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=OF(EN(DAG($Q5)=DAG($A$3)+3;MAAND($Q5)=MAAND($A$3));EN(DAG($Y5)=DAG($A$3)+3;MAAND($Y5)=MAAND($A$3));EN(DAG($AM5)=DAG($A$3)+3;MAAND($AM5)=MAAND($A$3)))"
        alledata.FormatConditions(1).Interior.ColorIndex = 37
    alledata.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=OF(EN(DAG($Q5)=DAG($A$3)-1;MAAND($Q5)=MAAND($A$3));EN(DAG($Y5)=DAG($A$3)-1;MAAND($Y5)=MAAND($A$3));EN(DAG($AM5)=DAG($A$3)-1;MAAND($AM5)=MAAND($A$3)))"
        alledata.FormatConditions(1).Interior.ColorIndex = 16
    alledata.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=OF(EN(DAG($Q5)=DAG($A$3)-2;MAAND($Q5)=MAAND($A$3));EN(DAG($Y5)=DAG($A$3)-2;MAAND($Y5)=MAAND($A$3));EN(DAG($AM5)=DAG($A$3)-2;MAAND($AM5)=MAAND($A$3)))"
        alledata.FormatConditions(1).Interior.ColorIndex = 48
    alledata.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=OF(EN(DAG($Q5)=DAG($A$3)-3;MAAND($Q5)=MAAND($A$3));EN(DAG($Y5)=DAG($A$3)-3;MAAND($Y5)=MAAND($A$3));EN(DAG($AM5)=DAG($A$3)-3;MAAND($AM5)=MAAND($A$3)))"
        alledata.FormatConditions(1).Interior.ColorIndex = 15

' kleuren: vandaag : 5 - toekomst : 41 33 37 - verleden : 16 48 15
End Sub

thank you for all the help !!!