Hi friends,
I'm getting error 1004 in the following code. Please suggest me the correction.
Column 't' contains the birthdates of persons. My data is in range 'b8:ae3000'. My requirement is:
If column 't' contains the today's date (current date) then the entire row range 'b:ae' should color green and as normal black on next day.
Please suggest me a solution.
Please Login or Register to view this content.
Thanking you in anticipation.
Regards,
Mukesh
you have specify row number ex: T1, T2, T3
if you wanted to loop through entire T column use this below codePlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by venkatpvc; 02-22-2014 at 12:33 AM.
Give Feedback and Click(*)
Sorry for incomplete information. The column 't' contains the birthdates of persons. On the birthday of the person the entire row should color green. No matter what's the year is. For example: If the cell 't10' contains the date 22/02/1999 then the entire row range 'b10:ae10' should color green.
Please see the attachment.
Thank you.
Mukesh
Please Login or Register to view this content.
Hi, Mukesh,
maybe try it like this:
Ciao,Please Login or Register to view this content.
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Hi venkatpvc,
Thank you for your cooperation to solve my problem. The code is working fine but the color is not changing after changing the date in column 't'. Ok! doesn't matter. The code suggested by Holger is giving the required output. Please don't mind. I appreciate you for your kind cooperation and efforts to solve the problem. Thank you.
Mukesh
Hi Holger,
Excellent! It's working absolutely fine with a correct output as per my requirement.
Just for my knowledge, what does the number 23 means in the code.
I'm getting message 'you must spread some reputation before giving hahobe again'.
What is wrong? Sorry!
Thank you.
Regards,
Mukesh
Last edited by mukeshbaviskar; 02-22-2014 at 09:36 AM.
Hi, Mukesh,
SpecialCells(xlCellTypeConstants) has 4 subtypes:
If you add them up (which means allow any type of Constant values in the cells) you get the "mysterious" number of 23. As there must be Dates in the cells and these Dates are real dates, not mumeric text, i could have used 1 instead. But I wanted to include every possibilty so I choose the more comprehensive number of 23.Please Login or Register to view this content.
You need to spent reputation on somebody else before you are able to spend again on the same person. In ExcelForum it´s only 1 other person you need to "donate" to with this being laid down in the Admin Settings of vBulletin.
HTH,
Holger
Hi Holger,
Good morning!
Thank you for explanation to improve my knowledge of vba.
Now I have one idea in my mind that is if we compare the current date in cell 'b4' which comes automatically after running the code daily.
The code in this workbook, workbook_open is:
If we compare the dates in column 't' with cell 'b4' day and month then we need not to run this macro daily. At present if I update any date in column 't' then I have to run the macro to update it.Please Login or Register to view this content.
Suggestion is:
If the day and month in cell 'b4' is = to the day and month in column 't' then it should color green. B4 = current date
If it is possible then do it otherwise leave it. I will put this code in this workbook code to run it automatically using call event. I don't want to change the output of the code but only want to link up it with the current date in cell 'b4' to do it automatically.
Thank you and have a nice day.
Regards,
Mukesh
Hi, Mukesh,
what do you really want to achieve? Me to rewrite every bit of code that you have gathered from the Forum so far?
You mentioned the given cell but that cell will hold the date after the code is run. So it might be possible to adapt the original macro to your needs but I doubt this should be part of me participating or be part of this thread.
Ciao,
Holger
Hi Holger,
Sorry! I asked you to whether it's possible or not. Don't mind. I don't want you to rewrite each and every code. I want to learn coding from you and the forum friends.
I have inserted the macro in this workbook call event and it's running without error. The output is as per expectation.
I don't want to trouble you more. Now my problem is solved.
I once again appreciate you for solving my problem.
Thank you and have a nice day!
Regards,
Mukesh
Hi Holger,
Good morning!
Sorry! Today I'm facing a problem in the code 'run-time error 13 time mismatch' on the following line.
The error is in the line:Please Login or Register to view this content.
Sorry for trouble again.Please Login or Register to view this content.
Thank you.
(The problem is related to this thread so I posted it here though the thread is solved and didn't start a new thread. Sorry for it.)
Regards,
Mukesh
Hi, Mukesh,
Without your workbook I would expect one or more cells not to be dates (maybe a header).Please Login or Register to view this content.
Ciao,
Holger
Hello Holger,
Thank you. Now the error is gone but no output. I'm attaching the workbook for your reference.
Thank you.
Regards,
Mukesh
Hi, Mukesh,
Ciao,Please Login or Register to view this content.
Holger
Hi Holger,
Excellent! Now it's working fine.
Problem solved.
Thank you and have a nice day!
Regards,
Mukesh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks