Instead of Range being By Column, Making for a Certain ColorIndex

[Codeman553]

How Would i make a range of a Intersect Statement be a color instead of columns.

Kinda like this but functional....

Please Login or Register  to view this content.

[xladept]

Hi Codeman,

You can test each cell in the Target Range:

Please Login or Register  to view this content.

[Codeman553]

OK so how would i change this function from making the range a column to a Color

Please Login or Register  to view this content.

[xladept]

If I understand what you're doing then Maybe:

Please Login or Register  to view this content.

[Codeman553]

It didnt work. So Basically i have a Time Entry Sheet that we will have filled out. I am trying to make it easier when entering time and that is why this code takes out the need of a colon. The problem is that the Entry Spots are not all linear and so i figured defining the range by color instead of column would work out better.

What i want the code to do is If the Cell color is "yellow" than it does the time format otherwise excel does its normal thing.

[xladept]

Hi Codeman,

I always edit after posting - try it now - it's tested and working for me - sorry!

[Codeman553]

Its Works!!!! Thanks!

While i have you hear how could you make it so that if i type say just 12 it would convert to 12:00 instead of 12:12.

[xladept]

I'll get back to you on that.

[Codeman553]

It Just comes back as 12:00 now no matter what.

[xladept]

Hi Codeman,

I couldn't make the Time functions work with any regularity. See if this works for you:

Please Login or Register  to view this content.

[Codeman553]

I Put your code in and it just made the number some long date. So i assume it doesn't work. The Single Digit part isnt very important i was just wondering if there was something simple that could be changed to make it work.

[xladept]

Hi Codeman,

What are you entering? It worked for me:
when I entered 12 - I got 12:00 PM
when I entered 13 - I got 1:00 PM etc???

[Codeman553]

Never mind it is working now but when i put in something like 230 it gives me the long date

[xladept]

Do you want code to handle an entry like 230??

[Codeman553]

Ya because when we input time into it generally 99.9% of the time it will be formatted like this #:00, #:15, #:30, #:45 AM/PM. I just figured if it would be quicker if it could handle putting in a single 2 to mean 2:00 instead of 12:02

[xladept]

Do you run more than one shift? Is an entry of 730 7:30 AM and an entry of 1:15 1:15 PM? What are the criteria for the entries - will 2:30 PM be entered as 1430???

How shall we change this?

Please Login or Register  to view this content.

[Codeman553]

Yes the guys clock in multiple times a day. Up to 10 clock in and clock outs in one day.

[Codeman553]

I need it to handle entering PM or P for PM. right now if i put 5p or 5pm it does 5p: than errors.

[xladept]

Hi Codeman,

Here's the next version

Please Login or Register  to view this content.

[Codeman553]

Works Wonderfully! I really appreciate all your help. Just started in the VBA area. I come from C# and Java background. Thanks again man!!!!

Edit:
Did find one bug when i type something with "AM" i get 23:0m AM.

[xladept]

Hi Codeman,

Just one bug? I'll check that out - I want to take the processing out of the range anyway - anon!

[Codeman553]

Ya as far as i have seen thats it. Thanks!

[xladept]

Hi Codeman,

I think I fixed that bug and I put the processing in core where it belongs it's not as pretty as I'd like but it may work now. C# and Java huh? I guess it's a different mindset???

Please Login or Register  to view this content.

[Codeman553]

OK ill take a look. Ya i understand the most of the global syntax that most language shared its just trying to learn specific syntax of vba that i am struggling. Been out of the game for a while now.

[xladept]

Hi Codeman,

Replace(!) the last routine with this:

Please Login or Register  to view this content.

[Codeman553]

Haha i remember those days when although something works you find it atrocious to look at so its time to clean it up. Just replaced it works great.

[xladept]

Hi Codeman,

Those days never die - they just fade away