VBA Change Effective Date based on Cell value criteria.

Hi,

I have column with many different effective date and which may not begin with Sunday dates. Is it possible to do a macro which change the date to Sunday Date for each cell if date is not equal sunday date?

Thanks & Regards,
Shiva

I guess you already have prior experience with VBA.. and you can adapt below formula in VBA..

Formula:

Please Login or Register  to view this content.

or please upload a sample file.. for structure of sheet..

Hi,

Sorry. Having issue with uploading document.

1) Column I is the Start date which I want to be a Sunday Date. Based on a below condition.

a) If, Column Q cell value is BW and Column I date is >= 06/30/13, else highlight the row to Yellow color.
b) If, Column Q cell value is BS and Column I date is >= 06/16/13, else highlight the row to Yellow color.
C) If, Column Q cell value is BA and Column I date is >= 06/09/13, else highlight the row to Yellow color.
d) If, Column Q cell value is BZ and Column I date is >= 07/01/13, else highlight the row to Yellow color.


Thanks
Shiva

90 views so far.

Is this code possible or too much coding required?

Thanks..

After 2 days of trial n error, I was able to make up a code which changes the dates to a sunday date.

Please Login or Register  to view this content.

but, I am not able to get the coloring right for cell. The problem I am facing are

1) Some cell getting colored to yellow, even though the dates are Sunday Date.
2) When I second time run this macro, every cell is getting colored to Yellow.


Appreciate, if someone have any tips on it.

Thanks

I believe your reply was not saved.

like??? what???

ohhk.

I updated entire column Q till data but still same issue.

Here is the file

Thanks

Sorry. I just missed to update the workrage to

Please Login or Register  to view this content.

from old

Please Login or Register  to view this content.

File is same just incase you get debug error messages.

Thanks

woo.. Thats was very fast...

Cool.....

One more query though. I was able to code it as per single condition. But if you look my original post, I have been trying to do it for 4 different condition.

a) If, Column Q cell value is BW and Column I date is >= 06/30/13, else highlight the row to Yellow color.
b) If, Column Q cell value is BS and Column I date is >= 06/16/13, else highlight the row to Yellow color.
C) If, Column Q cell value is BA and Column I date is >= 06/09/13, else highlight the row to Yellow color.
d) If, Column Q cell value is BZ and Column I date is >= 07/01/13, else highlight the row to Yellow color.

Can these be coded on a single macro above?? or Should I need to create 4 different macros??

Thanks

I get this error message

Please Login or Register  to view this content.

Removed the spaces

Please Login or Register  to view this content.

it work now..

just for my learning. These days, I am playing around with offset function.

So in your above code, when Column I changes to Sunday date; how can I add the offset , so the next cell (Column J) changes to Next Saturday date??

Or should I rebuild the code for Column J, Instead of using offset function here?

Thanks

Not sure what went wrong, I added offset like below.

Please Login or Register  to view this content.

Result is

PHP Code: 

Start Date*    End Date
      2013-04-04    2013-08-17
      2013-06-15    2013-08-17
      2013-04-07    2013-08-17
           FALSE    2013-08-15
      2013-06-30    2013-08-17
           FALSE    2013-08-17
           FALSE    2013-08-17 


I added offset code only at BW cell. So I got error only with cell value of BW.

Thanks it works..

What it is doing now is, if there is Date 08/15/2013, it goes back to previous Saturday to 08/10/2013. But If I want to make it coming days Saturday, i.e 08/17/2013?? then how?

Note: Sunday function perfect. I am just curious to understand, how to make it cell changes to Next saturday on next column cell..

Thanks.

Here it is..

Thanks

In Column J9, effective date I had entered is 08/15/2013. VBA turns the date to saturday date, but of previous week i.e 08/10/2013.
I am trying change the date to coming Saturday date , i.e 08/17/2013.

I hope, I am not confusing you again..

Thanks

ohhk..

Thanks yudlugar!!

Regards,