VBA for comparing dates within conditional formatting?

[HelenW]

Hi everyone

I hope someone can help with this - I am slowly trying to work my way through the VBA code I am writing, but bumbling through is not solving the conditional formatting I am trying to add!

I have no idea where to start with this, code-wise, so have nothing to paste. I have cells H2:M2 that I need to work with (although this will apply to all data in the sheet once I have worked it all out!), and I have a small piece of code that inserts todays date into cell O1, then uses paste special to put it back just as the value.

First of all, I have a condition as follows:

Please Login or Register  to view this content.

I then need to add (initially!) a conditional format that will compare the date in L2 with todays date in O1, and apply a different colour to the same cells (red) if todays date is greater than the one in L2.

I think if someone can help me with that bit then I can work through the rest!

Thanks

[royUK]

To add the date to a cell

Please Login or Register  to view this content.

No need to paste values with the above

Why do you need to use VBA to add the Conditional Format?

[HelenW]

Ah, thank you! I'm learning all the time with this, and loving it so far (although it's very frustrating some times!).

I need to use VBA as I will have more than 3 conditions when I have finished - I just needed help with the format of the particular one I listed, if you have any ideas?

[HelenW]

Hello again - I'm being dense, I'm sure, but I don't know how to use that piece of date code you gave me!

How do I tell it which cell to put the date into, please?

[royUK]

You cannot add more than three conditions like this in a version less than Excel 2007 even with VBA.

What are you actually doing

[HelenW]

Oh dear - well, any help you can give would be gratefull received!

I have various conditions that I need to fulfil, and I was hoping that if you could help me with the first one then I could work out the rest!

How will I do it if I have more than 3 conditions? I will try and lay them out below.

If I2 is blank, then H2:M2 coloured blue
If I2 is blank AND L2>O2 (todays date) then H2:M2 coloured red
If I2 is filled then K2 and M2 will either say Yes or No, depending on other date conditions, therefore I need...
If K2 AND M2 both = Yes then H2:M2 coloured green
If K2=No and M2=Yes then H2:I2 coloured green and L2:M2 coloured green (J2:K2 blank)
Finally, I need H2:M2 to be coloured orange if K2 AND M2 both = No.

I think that's it! Oh, and if once the formatting has been done, is there a way to just leave the colours in place, but not have the conditional formatting "code" in the background (like pastespecial with values)?