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

Tony, not entirely sure I follow but the below might give you a way forward ?

Please Login or Register  to view this content.

To insert the above right click on tab against which the formatting is to be applied select View Code and paste above into resulting window ensuring Macros are enabled thereafter.

I've obviously not tested the above run against 50000 cells simultaneously and I'm running on a machine with 4GB RAM so performance for me was ok on the limited test I conducted but I can't guarantee the same for you.

Originally Posted by Fidd$

The rows of data are divided up by subtotaling and obviously ranges have totals row under them, is it possible to modify the code so that those rows are not affected by the formatting? i.e. where there is a blank in J2 then nothing to be done to L2

Yes, one of the things I like most about the temp formulae approach is the ease with which you can modify requirements... ie

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

better IMO here to use an embedded IF rather than AND as need not compute the datedif calc for those subtotals rows.

So in effect what the code is saying is IF column J cell has nothing then exit, IF it does then check criteria for itself and also adjacent column L cell.
Please correct me if I'm wrong here
I am still very inexperienced in VBA, although I can understand what it kind of does by going through the code I'm not entirely sure exactly what and where it happens. Sorry to complicate things!

Correct.

I'm pressed for time today so I can't really spend the time explaining as I would like to but in essence the code works along the lines of:

- Disable Events etc to keep code as efficient as possible

- Establish "L" range (ie rows to process)

- Remove formatting from all rows

- Create a temp column of formulae in the last column on the sheet

- Get temp formulae to return either "X" (format) or 0 (do not format)

- Use SpecialCells to quickly identify & format all rows based on formula results ("X" being text and 0 being numbers - you can differentiate)

- Remove temp formulae column

- Re-establish Events etc... (ie restore environment)

If you want to remember you can step through the code using F8 - if you insert a breakpoint in the code (press F9 at an appropriate line) when the code is invoked you can press F8 to step through line by line and follow the impact on the sheet by moving between VBE and Excel
(the formulae will go in last column to right [whilst they exist])

ACH!!! Slight niggle. I've put the edited code in and it forces the subtotal rows to 'no fill'. I actually want to be able to (for once!) manually format those rows, only for presentation sakes.
Or, would it be better to have the code modified to do it for me?

You've already helped me loads so no worries dude
Thanks for the explanation too and I also didn't realise you could use F8 to go through line by line on the fly, that will help immensely.

Tony

You will want to format the Subtotal cells as part of the routine (ie at the end).

If we assume the subtotal rows can be identified by the fact they are the only cells in the range where J is devoid of content then you can perhaps revise the temp formulae to:

Please Login or Register  to view this content.

and then modify the subsequent code from:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

obviously change format(s) as required...

You need then to clearly state how the Subtotal rows can be differentiated from non-subtotal rows given J is not the determining factor in it's own right.

Originally Posted by Fidd$

Is it as simple as changing this ... to this...

It is......

Post a small sample so I can see exactly what your setup is.

What's happening now is that if both J and L are blank, it get's highlighted red. If I put something in L it highlights it black (colour of choice for subtotal rows).
Also, you'll kill me for this, column L is not all filled in, there are blanks in it too, which I have had to fill in with "." for the purposes of analysing.
Excuse the crap data set, it was given to me via a not so reliable filing company and I've been given the task of trying to get it to a decent state.

no attachment

If L contains text that explains why some non subtotal rows are being formatted as subtotals given the DATEDIF calc will (given present arrangement) generate errors, if blank the DATEDIF will generate result > 10/15 years (blank being 0 and 0 being 0 Jan 1900 (1900 date system))

perhaps:

Please Login or Register  to view this content.

Based on the file you added I would suggest you base your initial range detection on A rather than L and adjust remaining code accordingly, eg:

Please Login or Register  to view this content.