• 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 formatting on date

J

Jagdev Singh

Active Member
Hi Experts
Please help me with setting the specific condition on the date’s column. Once the date is added in the cell, from that date till next 7 days it should allow to change or update the date, but after 7 days if someone tries to change the date it should change the back ground color of the cell.
Regards,
JD
 

Attachments

  • sample.xlsx
    14.8 KB · Views: 7
Hi Jaggi ,

Can you explain the format of the data in column A ? Will it always be in this format ?

Also , can you explain this statement :
Once the date is added in the cell, from that date till next 7 days it should allow to change or update the date
Click to expand...

The date you enter into a cell in column A can be any date , in the past , present or future ; is this correct ?

If it is correct , then any modification of this cell's data is permitted for 7 days from the date the cell was populated , or is it 7 days from the date entered in the cell ?

Narayan
 
Last edited:
It will be in the way it is in the sample file, but is it possible to keep the condition even if it is not in a agreed format.

Regards
JD
 
Hi Narayan

The format of date should be "9/12/2014"

The date will be the present date. Like if today I will modify the date say 9/02/2015, till 16/02/2015 I should be allowed to modify the date. When I try to change the date on 17/02/2015 it should not allow me to change or change the color of the cell whichever way feasible.

Regards,
JD
 
HI Jaggi ,

See if this is acceptable.

I am not using the data in column A because it is not in the proper date format.

Narayan
 

Attachments

  • sample.xlsm
    20.7 KB · Views: 6
Hi Narayan

I am getting error when I am trying to open the above macro. Please find the error sceenshot attached with the thread.

Regards,
JD
 

Attachments

  • Capture.PNG
    Capture.PNG
    14.1 KB · Views: 2
  • Capture2.PNG
    Capture2.PNG
    3.8 KB · Views: 1
Hi Narayan

I tested your above code on a live data and it is not working as per my requirement. Please find the macro file attached where I am trying to run the code. My actual date is in column G and What I did I added the dates in this column in the first week of the March and today when I am trying to change the date it is not throwing me any error msg or neither changing the color of the cell. Could you please let me know what went wrong here.

Regards,
JD
 

Attachments

  • Sample.xlsm
    31.8 KB · Views: 2
Hi Narayan

I have few more sheets as well in this macro, but I need to set condition on this sheet "RawData" on column G.

Regards,
JD
 
Hi ,

I am sorry but this has to stop ; please upload your final working file once and for all. Saying it works on a sample file and then pointing out that it does not work on your working file , and that too after one month , what can I say !

Narayan
 
Hi ,

See the attached file , and please try to understand the code before trying to implement it in a different file or with different settings. I am ready to explain everything now , but not one month later.

There is nothing in the code to change any cell color ; I cannot say now whether there was any CF rule or not in the earlier uploaded file , but this present file does not have any.

Narayan
 

Attachments

  • Sample.xlsm
    29 KB · Views: 4
Hi Narayan

I remember there was no CF applied before. That is fine if it won't allow the color change, it is restrict the user to change the date cell that is fine.

Please help me understand the code, so that I implement it properly in the live data file.

Regards,
JD
 
Hi Narayan

I just checked the sample file you sent to me. It is not allowing even me to add the date in the blank cells.

Let me give you the short view what was my requirement before.

I will be adding the date every 15 days in this column, once I added the date then the user should be allowed to change the date from next 7 days. after the 7th day if someone try to change the date that cell should either change its color or get freeze and do not allow change.

Example if I added the first date - 16/03/2015 and till 23/03/2015 the user are allowed to change the date and from 24/03/2015 the respective cell should either change its color or get freezed.

Regards,
JD
 
Hi ,

There is a module which has been used to declare a public variable :

Global PrevValue As String

This used to store the earlier value of the cell in column G , so that if it is changed when it is invalid to change it , the previous value will be restored.

When the workbook is opened , the Workbook_Open event procedure runs , and does the following :

1. Activates the RawData tab

2. Stores the value in cell G2 in the PrevValue variable.

If you intend to implement this feature in some other tab , you will need to change the tab name used in this procedure from RawData to what ever is the tab name where you want this feature to be implemented.

There are 2 more event procedures in the sheet section ; these are :

1. Worksheet_SelectionChange

2. Worksheet_Change

Both of them implement almost the same logic ; the first one comes into effect when you move the cursor into the column which is being validated for the date.

Here it is column G ; if you intend to use a different column , you will have to make the change in both the above 2 procedures , replacing all references to column G by references to the column where you wish to implement this feature.

The code makes use of a helper column , which in the code is column AE ; since this is 24 columns away from column G , you will find that an offset of 24 columns has been used in the code. If you decide to use a column other than column G , you can leave this offset unchanged at 24 , which means that instead of column AE , which ever column is 24 columns away from your used column will be used as the helper column.

The reason for this helper column is to store the date when a new entry is made in column G ; as long as there is no data in the cell in column G , the corresponding cell in column AE will be blank. Now , when a date is entered in the cell in column G , say G13 , the code immediately enters the current date in AE13.

Thereafter , when ever you try to change G13 , the code checks whether the date in AE13 is older than 7 days from today's date ; if it is , it will not permit the change , and even if the change is made will restore the earlier value.

As long as AE13 is not older than 7 days from today's date , you can make changes to G13. When ever a change is made to G13 , the date entered in AE13 is not changed.

If you do wish to change G13 , all you have to do is clear AE13.

Narayan
 
Hi Narayan
Thanks for the explanation!
I have few queries
In case if a user wants to change the date or I want to change the date which is not fulfilling our set requirement then we need to clear the AE cell field of that respective row right?
Can we set CF for the same macro? Like setting up the condition say I will be adding the date in the column G and if someone try to change the date that is current date + 7 days is fine and after 7th day the cell’s color will get change. Just asking for my understanding.
Regards,
JD
 
Hi ,

To change the date , just clear the corresponding cell in the helper column.

If you want to add CF , go ahead and add it ; the code will not remove it , and the CF will operate along with the code ; in a way it will be good because the code will display a message only when the cursor is placed in a cell in the date column. The CF will change the cell color and this will be visible always.

Narayan
 
Hi Narayan

Could you please help me with the CF formula. I am trying this "=$G$2:$G$1048576<=7" I don't think this is the correct come.

Regards,
JD
 
Hi Narayan

Thanks for the CF formula - =AND($AE2<>"",(Today() - $AE2) >= 7). Could you please help me understand why are we using (Today()) function in this formula.

If I am not wrong the Today() will change the date everytime we will open the sheet, Say a day after I modified the sheet.

please let me know if I am wrong here.

Regards,
Jagdev
 
Hi ,

Let me try and understand ; the code that is in your file works as follows :

Suppose you enter a date in a cell in column G , say G10 ; if this is a new entry , then the corresponding cell in the helper column , column AE , which will be AE10 , will be blank. In this case , the code will put today's date in AE10.

Thereafter , when you try to modify G10 , the code looks at the date in AE10 , and sees whether it is 7 days prior to today's date ; if it is not , then the modification is allowed , otherwise , it is prevented. So if today's date is 17 March , an entry made today can be modified till 24 March ; on 25 March if you try to modify G10 , the code will prevent any modification.

The CF formula is doing the same ; if the cell in column AE is not blank , and the date in that cell is not 7 days earlier than today's date then the corresponding cell in column G is colored.

If you were looking for something else , please explain.

Narayan
 
Hi Narayan
I get your point of adding today’s function in the CF.
Please let me know if I am wrong here. I am trying to understand the procedure
My requirement is like in column G I will be adding a fixed date “17th March, 2015” in all the cells. This 17th march will be saved in helper column AE. Now, after this any user will be allowed to change the date till 24th March, 2015. Once this date is crossed, if anyone tries to change the date it will change the color of the cell or freeze the cell.
Also, please let me know in the above example, I have to delete the old value saved in column AE of the macro right? Every time I need to refresh the entries in the macro. What if I apply the formula (Now()) to capture date in the column will the helper will be updated automatically?
Regards,
JD
 
You must log in or register to reply here.
Back
Top