Macro to apply colour conditional formatting to four columns

Hi,

I am new to writing Macros for Excel. I am having issues with conditional formatting. I have a table with three distinct row sections, so it is necessitated that I insert rows within the middle of the table. When I do that, the range of my conditional formatting is altered, and a new set of formulas is produced. This is slowing down the program and making it hard to use.

I thought I could write a Macro to remove all of the formatting from the table, and then apply some colour conditional formatting to four columns based on some formulas.

The basic layout of my spreadsheet includes a column for each of the following: four week date, five week date, six week date, and sent date. I have no problems getting excel to calculate these dates, but I need the cells to be coloured in a couple of different ways and for different reasons.

• No formatting of any of cell if the four week date in that row is blank
• Four week date cell coloured yellow if today's date is greater than the four week date, and the sent date column is blank. (Stop if true)
• Four week date cell coloured yellow if the four week date is greater than the sent date
• Five week date cell coloured orange if today's date is greater than the five week date, and the sent date column is blank. (Stop if true)
• Five week date cell coloured orange if the five week date is greater than the sent date
• Six week date cell coloured red if today's date is greater than the six week date, and the sent date column is blank. (Stop if true)
• Six week date cell coloured red if the six week date is greater than the sent date
• Sent date cell coloured red if today's date is greater than the six week date, and the sent date column is blank. (Stop if true)
• Sent date cell coloured red if the six week date is greater than the sent date

I wrote this, based on some examples I saw online. It isn't working, and is incomplete.

Please Login or Register  to view this content.

Any help or advice would be appreciated. I've been looking for ages online for some guidance, but haven't been able to find what to write in a Macro to complete these tasks. If I need to make anything clearer, please let me know. Thank you very much.

Moderators Note:

• Please follow Forum Rule #3 and use code tags.
• Added this time, but please use them in the future…Thanks.