VBA Conditional formatting (Less than / Greater than) for numerous ranges

Hi!

With a help of samples and Google I've created this VBA code (on Worksheet_Change)

Please Login or Register  to view this content.

(Shows how little I know about VBA, really)

Thing is my data is loads of numbers in range of A2:AE5350 (and growing daily (row-wise)
but each column has a different Less than/Greater than targets.

Above code works fine for column A, where criteria is as per code, but what do I do to replicate
this code so it applies same conditional formatting for rest of the columns, with different criteria for each?

Do I just simply duplicate :

Please Login or Register  to view this content.

For/Next bit and change the Range and Values and so on for each column up till AE?
Also I think I need to increase the row count from current 5350 (number of actual records) to something like 10000 so
that it lasts for some time, without changing the code again, right?

Please help.

Regards,
Janis

Hi Leith,

Thank you for welcoming.
No, each column will have different conditional test. Values will differ.

Regards,
Janis

Wow, thank you Leith. This is much more complicated than I thought it will be. You done too much work. I appreciate it very much.

What does variable "r" stand for? I cannot see it defined anywhere and code gives an error because of it.

Kind regards,
Janis

Strange, I added

Please Login or Register  to view this content.

but when executed, code still gives me an error: "Compile error: Type mismatch" in

Please Login or Register  to view this content.

as for colour coding, yes, I will have only three colours - red, green and black across the whole data range.

Regards,
Janis

Here you go Leith. I am currently at work and don't have the original file at hand as it is not work related, but I created a good example of what it looks and contains.
Hope it helps.

Regards,
Janis

Massive, massive thank you, Leith. You helped alot.

Regards,
Janis

Hi Leith,

Can you please tell me how do I make this code to run through my existing data, as is seems it only applies criteria to new data added?

Thank you.

Also I think I forgot about something hence giving you misleading information - my data range actually is B2:AE2 not A2:AE2
and in this data range there are columns I don't need conditional formatting criteria to be applied. I'm sorry I didn't tell you this early.

Anything you can do about it now?
Thank you.
Janis

copy of actual spreadsheet (part of it)