Conditional Formatting using VBA

Morning,

I have a spreadsheet in Excel 2003 and with conditional formatting only allowing for 3 formats I have had to resort to VBA to format individual areas of the sheet.

I have some code which works well, but doesn't quite do what I need.

In column R there is a calculated number. This is the number of days something has taken to be returned to me. I want my VBA to format the column J based on the corresponding cell in R.

e.g. R5 = 60 then J5 should be red
R6 = 35 then J6 should be orange

The current VBA does this, but only for the cell in R - I don't know how to to change the cell to J.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You could also use rCell.offset(0,-7) to return a cell relative to another.

Thanks to all - the formulas worked well.

@ SixthSense,

I'm not sure about that code, I think union gives an error if one of the arguements is empty so if you have only formulas in your range, rMyRng will be empty after you run this code:

Please Login or Register  to view this content.

It would only work if you have only constants or both constants and formulas I believe.

I wasn't 100% sure!

@ OP, using Sixthsense's method, you can do this that would run quicker than my code and work for formula only instances:

Please Login or Register  to view this content.