CODEWITHSUNDEEP
excelvbaexcel-formula
Mr.M
Mr.M

Reputation: 1490

set 2 reminder date using excel vba / conditional formatting

I am trying to learn VBA in excel.

Here is my column structure.

 G            H                   I                  J             K       
 Email Sent   Email Sent date     Remainder 1        Remainder-2   Client Reply
    Yes            05-Aug-2019

Once my email sent Marked Yes and Email sent date was available automatically Reminder 1 (6 Working days) date and reminder 2 (9 working days) date should appear with colour code for Reminder 1 it should be orange and reminder 2 it should be Yellow

Currently I am trying with Conditional formatting but that is not helping me what I am looking for.

I tried the below code but I am not seeing the result. What am I doing wrong?

=AND(H2<>””,H2<TODAY()+7)

I have tried the below formula for adding dates

=IF(G215="Yes",WORKDAY(H215,6),K215="Yes""")

the above code was working perfectly fine but only problem is if client Reply column value select Yes those date should clear

Upvotes: 0

Views: 156

Answers (2)

Mr.M
Mr.M

Reputation: 1490

Thanks for your explanation before your code I have completed my code.

below code is for 6 days

=IF(K341="Yes","0",IF(G341="Yes",(WORKDAY(H341,6)),"0"))

Below code is for 9 days

 =IF(K341="Yes","0",IF(G341="Yes",(WORKDAY(H341,9)),"0"))

Upvotes: 0

Danilo G.
Danilo G.

Reputation: 95

This answer will solve your problem using Excel formulas only and not VBA. It is possible to solve the problem using VBA, but it seems simpler to solve using formulas and conditional formating only.

In column I (row 5, as example):

=IF(K5<>"yes";IF(G5="yes";WORKDAY(H5;6);"");"")

In column J (row 5, as example):

=IF(K5<>"yes";IF(G5="yes";WORKDAY(H5;9);"");"")

(Please note that I use semicolon instead of ony commas as separators, so you should adapt it to your Excel)

To format as Orange and Yellow each cell, you can use Conditional Formating:

Format only cells that contain -> Cell value | not equal to | = ""

Use the Conditional Formatting in columns I and J changing the cell fill.

Results when there is anything but "yes" in column K: The Conditional Formatting activates

Results when there is "yes" in column K: The Conditional Formatting deactivates

Conditional Formatting (use one rule for each column): Cond. Form. example

Upvotes: 1

Related Questions