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 ,
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
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.Hi ,
To give a precise formula , I need to know the column !
Can you upload a sample file ?
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 ,
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
WowHi ,
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!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
Hi Narayan,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
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 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
I've selected both. I closed the workbook and reopened it. the same notification again occurs.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,Hi ,
Are you able to run macros from any other workbook ?
Is it only this workbook which is giving problems ?
Narayan