Need a code to convert age formula as vba

[mukeshbaviskar]

Hi friends,
My data is in 'b8:ae3000'. I want to convert age formula in vba to reduce file size and get the output as value in column 'ae'.

The birthdate is in column 't' and age formula is in column 'ae'.

The age formula is as follows:

Please Login or Register  to view this content.

Secondly, If the column 't' contains todays date the the entire row 'b:ae' should colored green for that day and normal on next day as black.

Please suggest me a code to achieve this target.

Thanking you in anticipation.

Sincerely,

Mukesh

[vnzerem]

This should help you out using array method:

Please Login or Register  to view this content.

Don't forget to mark the thread as solved and also remember to click on the star

[vnzerem]

This second one will help you out also without using arrays and also color the row that you want as Green:

Please Login or Register  to view this content.

Don't forget to mark the thread as solved and also remember to click on the star

[mukeshbaviskar]

Hi VNZerem,
Good! It's working fine but I need some revision in it. I have made a little changes in the code to remove error and hide columns after running the macro. It reduce 112 kb file size which I want. Secondly, I want the output in value because when I go in edit mode in column 'ae' by pressing f2 then the cell shows formula rather than the age which I don't want.

I want to put this code in sheet code so that whenever there is change in column 't' birthdate, then the column 'ae' value should automatically change. I don't want to run the macro each time I open the file. I want to do it automatically.

Modified code:

Please Login or Register  to view this content.

The color of today's date is also not changing to green. I like the first code because it takes a very little time to complete rather than second code.

So please do the revision in the first code I have modified.

Thank you for excellent code.

Sincerely,

Mukesh

[vnzerem]

Hope you saw my second code? Please review it...I am pasting it here for you again

Please Login or Register  to view this content.

Don't forget to mark the thread as solved and also remember to click on the star

[mukeshbaviskar]

Hi VNZerem,
I have already seen your second code. It's working fine and giving output as value but if I change the birthdate in column 't' then I have to run the macro again to get correct output. It's taking a long time to run rather than first one.

We may add the end code of this copy/paste-value to first one to get output as value. It's also not changing the color of today's date to green.

Do think on my first request in last post to get desire output automatically without running the macro each time I change the value in column 't'.

Thanking you in anticipation.

Sincerely,

Mukesh

[mukeshbaviskar]

Hi VNZerem,
Now I have another option to solve this problem. I modified the code and used the call event. Only I get the changed output after next opening the file. Doesn't matter.

Now only last problem is to solve to color the today's birthdate row green 'b:ae'.

Please Login or Register  to view this content.

Thank you.

Regards,

Mukesh

[vnzerem]

Since you prefer the Array Method because it is faster, the code below should help you out. Don't forget to click on the STAR (Add Reputation) and mark the thread as SOLVED:

Please Login or Register  to view this content.

Note: You have to run the code whenever you update your spreadsheet.

[mukeshbaviskar]

Hi VNZerem,
Good! It's working fine but only one problem is there. It colors entire range having age in column 'ae' which I don't want. I want to color entire range of that particular birthdate. For example: If the birthdate in cell 't10' is today's date (current date) then only the entire range of that particular entry here 'b10:ae10' should color green and not other cells/range. In short only the row of current date should color green.

Please Login or Register  to view this content.

Thank you for revision to solve my problem. After this last revision the thread will be solve.

Thank you.

Regards,

Mukesh

[vnzerem]

Is working very fine on my end to color range B:AE green....please click Star if this was able to help you.

[mukeshbaviskar]

Hi vnzerem,
Thank you for your assistance to solve my problem.

Thank you.

Regards,

Mukesh

[vnzerem]

It is easier if you upload a sample spreadsheet with what you want to achieve. We are not mind readers!

[mukeshbaviskar]

Hi vnzerem,
Thank you. I'm attaching the sample workbook for your reference. In the directory cell 't30' contains today's date. As my requirement only the range 'b30:ae30' should be color green and not all the ranges containing age in column 'ae'.

If date is changed in column 't' then the range color should be as normal.

Thank you.

Regards,

Mukesh

[vnzerem]

Dude!
I don't know the kind of spreadsheet that you have, but I have copied your data to my spreadsheet and ran the code, and once again everything is working great. I have attached it here for you. Have a great day!

[mukeshbaviskar]

Hi vnzerem,
Excellent! The output in your file is absolutely as per my requirement but it's not getting on my pc. I don't know what's wrong with my pc. On my pc all the rows having age in column 'ae' changes to green color which is wrong output.

Actually, you have done a great job but I think it's a problem of my pc. So leave it. I will drop the color code from the code and work on it because except the color problem everything is perfect as per my requirement.

Thank you for taking trouble to solve my problem. I appreciate you for your kind assistance to solve the problem.

Thank you very much and have a nice day!

sincerely,

Mukesh

[vnzerem]

I think I know what the issue with your spreadsheet is: You are missing some object libraries.
In the VB Editor, click on Tools>Refrences> Then uncheck all the options that are showing “Missing”. For example it shows that you are Missing: Microsoft Outlook 12.0 Object Library. Make sure none is showing missing by unchecking them.

You can also remove the Option Explicit by going to Tools>Options> Uncheck Require Variable declaration, and also ensure that they are all removed from your project.

I tried this on your spreadsheet and it is working perfectly fine, and I have attached your spreadsheet here for you. I also added a Calculate button on your spreadsheet. Let me know how this goes.

[mukeshbaviskar]

Hi vnzerem,
Thank you for taking extra efforts to solve my problem. I really appreciate you for it. Still all the rows contain birthdates in column ‘t’ are being colored green which I don’t want. The sample file is perfect but after running the macro on my laptop all the birthdates containing rows colored green. I unchecked required variable and deleted all the option explicit from the file. But unfortunately I’m not getting the correct output. I think it’s a problem of the setting of my laptop. Hereby I am pasting the list of objects library in reference as follows.
I'm not getting any object library missing message in reference library.

Visual basic for application
Microsoft excel 12.0 object library
Microsoft office 12.0 object library
Microsoft forms 2.0 object libraries
Microsoft outlook 12.0 object library
Ole automation

Please see and tell me that do you want to add any other library to solve the problem.
Please paste a list of object libraries checked in your pc to check my mistake.

I’m optimistic to get the perfect output.

Thank you very much.

Sincerely,

Mukesh

[vnzerem]

Sounds like a computer problem to me....