• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

conditional date formatting

Ramin

New Member
Hi.I want to format a cell as an orange color after passing exactly 30 days from the time I have entered a number into it.. please help me. Thanks
 
Hi ,

Once you enter a number into a cell , will you modify it ? If yes , then will the coloring need to be done 30 days from this date or 30 days from the original date ?

Narayan
 
Hi ,

Once you enter a number into a cell , will you modify it ? If yes , then will the coloring need to be done 30 days from this date or 30 days from the original date ?

Narayan
once I enter a number, There would be never any modification....the coloring must be done exactly 30 days from the original date..... of cource this original date is the date that the number is entered.
 
Hi ,

In that case , a helper column will need to be used if you want to go with a formula-based solution ; the alternative is to use VBA. Which is your preferred option ?

Either way , can you upload a sample workbook , so that the column where you want to implement the coloring is known ?

Narayan
 
The columns are known, please tell me your formula-based solution, also I like to know your VBA, I like to know both.
 
Narayan, this is a file of mine.... the purpose is coloring a cell after 29 days from the time of entering the today single date number so that I can realize that someone's monthly fee is expired.... these numberes entered into the cell are in these columns.: D-I_N_S_X_AC_AH_AM_AR_AW_BB_BG . Note that the first rows of them are not required to be formatted(because there are texts and they are merged cells as you see in the file)...I mean these rows 1-2-3-4... The file is in persian and the password is 123456.. Thanks for your kind help Narayan.
 

Attachments

  • Ramin.xlsx
    308.5 KB · Views: 2
Hi ,

To give a precise formula , I need to know the column !

Can you upload a sample file ?

Narayan
Narayan, this is a file of mine.... the purpose is coloring a cell after 29 days from the time of entering the today single date number so that I can realize that someone's monthly fee is expired.... these numberes entered into the cell are in these columns.: D-I_N_S_X_AC_AH_AM_AR_AW_BB_BG . Note that the first rows of them are not required to be formatted(because there are texts and they are merged cells as you see in the file)...I mean these rows 1-2-3-4... The file is in persian and the password is 123456.. Thanks for your kind help Narayan.
 

Attachments

  • Ramin.xlsx
    308.5 KB · Views: 0
Hi ,

Now the problem is on a much bigger scale ; unlike your single-column example , now there are 12 columns , which means we will need 12 helper columns.

I have done it only for column D ; before we extend it to cover the other columns , let me know if it works.

Narayan
 

Attachments

  • Ramin.xlsm
    319 KB · Views: 1
Hi ,

Now the problem is on a much bigger scale ; unlike your single-column example , now there are 12 columns , which means we will need 12 helper columns.

I have done it only for column D ; before we extend it to cover the other columns , let me know if it works.

Narayan
Thank you Narayan. but unfortunately after entering a number,It changes to orange . passage of 29 days is needed for changing to orange.is it a good idea to change the computer date to see if it works properly?
 
Hi ,

In which cell did you enter a number ?

The code works as follows :

When you enter data in any cell in the range D5:D1004 , today's date is entered in the corresponding cell in column DD , in the range DD5 : DD1004.

When you enter data , the date put in column DD will be today's date ; I had manually changed the date to January 26 , so that it is more than 30 days before today , to check that the conditional formatting turns the cell color to RED.

Narayan
 
Last edited:
Hi ,

In which cell did you enter a number ?

The code works as follows :

When you enter data in any cell in the range D5:D1004 , today's date is entered in the corresponding cell in column DD , in the range DD5 : DD1004.

When you enter data , the date put in column DD will be today's date ; I had manually changed the date to January 26 , so that it is more than 30 days before today , to check that the conditional formatting turns the cell color to RED.

Narayan
Wow
Hi ,

In which cell did you enter a number ?

The code works as follows :

When you enter data in any cell in the range D5:D1004 , today's date is entered in the corresponding cell in column DD , in the range DD5 : DD1004.

When you enter data , the date put in column DD will be today's date ; I had manually changed the date to January 26 , so that it is more than 30 days before today , to check that the conditional formatting turns the cell color to RED.

Narayan
Narayan, I want all 12 columns to be performing as what we want. I couldn't understand what you've exactly done. please do your job on entire of column D and please teach me how I do so for the next 11 columns.I guess you have manually edited the date to check if it works properly or not. in this case after entering any number, The cells turn into orange. I don't know how to fix the date to solve this problem!
Thanks for your kindness
 
Hi ,

See this file.

How you can test this is as follows :

1. Make an entry in any of the following ranges :

Sheet1!$D$5:$D$1004
Sheet1!$I$5:$I$1004
Sheet1!$N$5:$N$1004
Sheet1!$S$5:$S$1004
Sheet1!$X$5:$X$1004
Sheet1!$AC$5:$AC$1004
Sheet1!$AH$5:$AH$1004
Sheet1!$AM$5:$AM$1004
Sheet1!$AR$5:$AR$1004
Sheet1!$AW$5:$AW$1004
Sheet1!$BB$5:$BB$1004
Sheet1!$BG$5:$BG$1004

2. Check that today's date has been put in any of the following ranges , in the corresponding cell :

Sheet1!$DD$5:$DD$1004
Sheet1!$DI$5:$DI$1004
Sheet1!$DN$5:$DN$1004
Sheet1!$DS$5:$DS$1004
Sheet1!$DX$5:$DX$1004
Sheet1!$EC$5:$EC$1004
Sheet1!$EH$5:$EH$1004
Sheet1!$EM$5:$EM$1004
Sheet1!$ER$5:$ER$1004
Sheet1!$EW$5:$EW$1004
Sheet1!$FB$5:$FB$1004
Sheet1!$FG$5:$FG$1004

For example , if you make an entry in cell AC17 , today's date will be entered in cell EC17.

The cell AC17 should not be colored.

Change the date in cell EC17 to a date 30 days before today's date , say January 25 , 2015.

The cell AC17 should be colored.

Delete the dates in the range DD5 : FG1004 when you are done testing.

Delete the trial data which you entered in the cell AC17.

If you face any problems let me now.

Narayan
 

Attachments

  • Ramin.xlsm
    320.5 KB · Views: 2
Hi ,

See this file.

How you can test this is as follows :

1. Make an entry in any of the following ranges :

Sheet1!$D$5:$D$1004
Sheet1!$I$5:$I$1004
Sheet1!$N$5:$N$1004
Sheet1!$S$5:$S$1004
Sheet1!$X$5:$X$1004
Sheet1!$AC$5:$AC$1004
Sheet1!$AH$5:$AH$1004
Sheet1!$AM$5:$AM$1004
Sheet1!$AR$5:$AR$1004
Sheet1!$AW$5:$AW$1004
Sheet1!$BB$5:$BB$1004
Sheet1!$BG$5:$BG$1004

2. Check that today's date has been put in any of the following ranges , in the corresponding cell :

Sheet1!$DD$5:$DD$1004
Sheet1!$DI$5:$DI$1004
Sheet1!$DN$5:$DN$1004
Sheet1!$DS$5:$DS$1004
Sheet1!$DX$5:$DX$1004
Sheet1!$EC$5:$EC$1004
Sheet1!$EH$5:$EH$1004
Sheet1!$EM$5:$EM$1004
Sheet1!$ER$5:$ER$1004
Sheet1!$EW$5:$EW$1004
Sheet1!$FB$5:$FB$1004
Sheet1!$FG$5:$FG$1004

For example , if you make an entry in cell AC17 , today's date will be entered in cell EC17.

The cell AC17 should not be colored.

Change the date in cell EC17 to a date 30 days before today's date , say January 25 , 2015.

The cell AC17 should be colored.

Delete the dates in the range DD5 : FG1004 when you are done testing.

Delete the trial data which you entered in the cell AC17.

If you face any problems let me now.

Narayan
Hi Narayan,
the testing failed. I'm sure something is going wrong. As you said,I made an entry in cell AC17. As soon as I made an entry,It turned to orange and today's date didn't entered in cell EC17 ! so there was no date to change to a date 30 days before today's date.
unfortunately it failed to work properly. waiting for your help Narayan.
 
Hi ,

I have just tried the exact same thing at my end , and it works the way I have described it.

See the attached file with 3 entries in column AC , and the corresponding dates in column EC.

Narayan
 

Attachments

  • Ramin.xlsm
    320.5 KB · Views: 2
Hi ,

I have just tried the exact same thing at my end , and it works the way I have described it.

See the attached file with 3 entries in column AC , and the corresponding dates in column EC.

Narayan
Narayan, you are right. It's OK. but can you make this rule for all of the cells in these 12 columns? If it's time consuming I'm ready to do this rule but yet I don't know what you have already done that it responds so beautifully. Thank you dear Narayan. except from these cells that you have made entries, other cells are not yet responding with your rule. for example when I make an entry in cell AC25, It turns to orange and also today's date wouldn't enter in cell EC25. As you know, we want every date enters automatically in specified helper columns and after passing 29 days,the cells turn to orange automatically. Waiting for your kind help Narayan.
 
Hi ,

I do not know what is the problem ; can you confirm the following ?

In your Visual Basic Editor , in the Immediate Window pane , type in the following :

?Application.EnableEvents

and see what is displayed ; you should see True. If you see False , then in the same window , type in :

Application.EnableEvents = True

and press Enter.

Have you enabled macros ?

Narayan
 
it was my first experience of activating the developer toolbar and seeing Microsoft Visual Basic editor.
I enabled all macros and also trust access to the VBA project object model.
in the pane of Microsoft Visual Basic editor, I entered
?Application.EnableEvents
and immediately changed to
print Application.EnableEvents

I couldn't see any true or false result.
 
Hi ,

When you open the Visual Basic Editor , you will see the following windows :

The one on the left side showing the worksheets , ThisWorkbook , and possibly one or more Modules is called the Project Explorer.

The one on the upper right is the Code window , where you can insert procedures and type out your code for execution once it passes all syntactic checks.

The one on the lower right below the Code window is the Immediate window , where you can type out statements and have them immediately executed and the results displayed in the same window. You can see the title bar of this window will display the word Immediate.

When you type out a statement such as :

?Application.EnableEvents

you need to do this in the Immediate window , not in the Code window. Only then will the statement be executed and the result displayed.

Narayan
 
Hi Narayan,
In the immediate pane, I typed
?Application.EnableEvents
then a notification window notified me that the macros are disabled, I did as follows to enable macros:
  • clicked on the ‘Office’ button.
  • Then clicked on ‘Excel Options’ (present at the bottom).
  • Next selected the ‘Trust Center’ > ‘Trust Center Settings’.In the trust center settings clicked on the ‘Macro Settings’ and enabled the last item(my version of excel is 2007)
again when I type it on the immediate pane, the same window pops up and notifies me.
 
Hi ,

There are 2 settings :

Enable all macros

Trust access to the VBA project object model


Have you selected both ?

After this , close the workbook , and reopen it , and then try.

Narayan
 
Hi ,

There are 2 settings :

Enable all macros

Trust access to the VBA project object model


Have you selected both ?

After this , close the workbook , and reopen it , and then try.

Narayan
I've selected both. I closed the workbook and reopened it. the same notification again occurs.
 
Hi ,

Are you able to run macros from any other workbook ?

Is it only this workbook which is giving problems ?

Narayan
Hi,
You are right. I could run macros in a new workbook.I typed
?Application.EnableEvents
and the response was
true
it seems the problems occur only to this workbook :( . What should I do Narayan?
 
Hi ,

I have no idea ! No workbook can affect Excel options to this extent.

Can you copy the code alone , the Module , the ThisWorkbook section and the Sheet1 section code to a new workbook , and see if it works there ?

If it works there , then it is possible in some way this workbook has got corrupted ; let me know , and I'll upload another time.

Narayan
 
Back
Top