Conditional Formatting with VBA

[onelifestyle]

Hello, I would like help with a Macro that would format a row based on the following:

Color the entire row RED (font color: White) if any cell in Column 1 = “Active” AND Due Date is 6 months away from TODAY().

Stay RED (font color: White) until any cell in Column 1 changes to Inactive (or any other text or blank) or Due Data changes to a date further than 6 months away from TODAY().

Thank you for your help!

[Leith Ross]

Hello onelifestyle,

Which column is "Due Date"?

[onelifestyle]

Hello Leith Ross, thanks for the reply.

Due Date is in Column N.

Also, right now there are 837 records (rows) but more will be added with time.

[Leith Ross]

Hello onelifestyle,

Do you want this to happen as the data is entered into column "A" or run a macro to format all the data at one time?

[onelifestyle]

Hi Leith Ross,

If possible it would be nice to format the row as a new row is entered, text in column "A" changes from Active to any other text or Due Date (column "N") changes to a date further than 6 months away from TODAY().

If that's not possible than running the Maco to format all the data at one time is fine.

Thanks!

[Leith Ross]

Hello onelifestyle,

Add a new VBA module to your workbook. Copy and paste the macro code below into it.

Please Login or Register  to view this content.

[Leith Ross]

Hello onelifestyle,

I will write another macro to format the data as it is entered.

[Leith Ross]

Hello onelifestyle,

This macro can be copied into any worksheet module. This will format the row as the data is entered.

Please Login or Register  to view this content.

How to Paste a Macro into a Worksheet

1. Copy the macro with Ctrl+C.
2. Right Click the Sheet Tab at the bottom of the worksheet.
3. Click View Code
4. Paste the macro into the module with Ctrl+V.
5. Save the macro using Ctrl+S

[onelifestyle]

Thank you. I did was you told me.

After I saved it nothing happened. For example, there's an expiration date (cell N837) of 8/24/2015 (that's within 6 months from TODAY()) and cell A837 is "Active" but the row did not turn RED. What do I have to do to run the code in the Module? Sorry I'm new with VBA. Thanks!

[onelifestyle]

Thank you. I did was you told me.

After I saved it nothing happened. For example, there's an expiration date (cell N837) of 8/24/2015 (that's within 6 months from TODAY()) and cell A837 is "Active" but the row did not turn RED. What do I have to do to run the code in the Module? Sorry I'm new with VBA. Thanks!

[Leith Ross]

Hello onelifestyle,

I have tested this macro and it works. Be careful of your dates. They need to be in day/month/year format.

Please Login or Register  to view this content.

[onelifestyle]

Thanks. I checked and the dates in column N are in the correct format.

I would also like to mention that I have this Macro in ThisWorkbook

Please Login or Register  to view this content.

[Leith Ross]

Hello onelifestyle,

Your macro is checking column "N" is equal to today's date. If it is then the word "six" is added to the email body.

What is your question?

[Leith Ross]

Hello onelifestyle,

Your macro is checking column "N" is equal to today's date. If it is then the word "six" is added to the email body.

What is your question?

[onelifestyle]

I apologize. I really didn't have a question on my last reply.

If you don't mind could you revise the attached file. The actual data has been replaced for security reasons. I'm hoping that if you take a look at the file you can tell me why the module does not run. I'm sure the code is fine. It may be something I'm doing wrong. Thanks! Clinical Contracts_ Format.xlsm

[Leith Ross]

Hello onelifestyle,

I don't know if you are having problems connecting with the forum, but I am. I did get your workbook and am looking at it.

[Leith Ross]

Hello onelifestyle,

I don't know if you are having problems connecting with the forum, but I am. I did get your workbook and am looking at it.

[onelifestyle]

I was having problems earlier. But it seems to work fine now. Thanks.

[Leith Ross]

Hello onelifestyle,

No problem, the code needed to be moved from Module1 to Sheet1. The attached workbook works like you want now.