Conditional formatting based on multiple criteria (needs to be in VBA)

Hi Everyone,

First off, in case anybody was wondering, I have searched in this forum for anything similar but haven't found anything I can easily modify for my own usage. I'd thought I'd let everyone know just in case someone suggests a search!

I have a large worksheet which I need conditional formatting on a range of cells based on it's own value and another cell's value on the same row.
For example, Cell L2 has a date value and cell J2 has a text value.

I want cell L2 to be highlighted IF the date in this cell is over 15yrs before today's date and cell J2 value is "Conveyancing" OR, if cell J2 is not equal to "Conveyancing" and cell L2 is over 10yrs before today's date to be highlighted also.

I have achieved this quite well with using the conditional formatting option under Format and it goes like this:

=IF(AND($J2="CONVEYANCING",$L2<DATE(YEAR(NOW())-15,MONTH(NOW()),DAY(NOW()))),TRUE,IF(AND($J2<>"CONVEYANCING",$2<DATE(YEAR(NOW())-10,MONTH(NOW()),DAY(NOW()))),TRUE,FALSE))

Now, it isn't that it doesn't work BUT, the workbook doesn't save all the formatting because (after a wee bit of digging around the MSDN forums....) there's a "limitation to how Excel saves information if there are more than 2050 rows with conditional formatting"............hence the need to do this in VBA.

I am dealing with a BIG sheet of data, around 50,000 rows of information and I need the formatting so I can easily identify rows where it meets the above criteria (and that's just the start!!!!)

Any help in achieving this in VBA would be mucho appreciated!

Thanks,
Tony