Colour Cells depending on time

[koala]

Hi everyone,

I am quite new at useing VBA and dont know how to do this.

Is anyone able to assist with some code to automate the process of colouring the interior of cells depending on the time entered.

I have attached a small sample so you can get a better understanding as it is difficult to explain.

Basically here is what is required.

The time matrix is on the sheet from columns J:BF (in half hour increments)

The time entered to start (column E), until the scheduled completion (column F) needs to be coloured blue on the matrix.

The time from scheduled completion (column F) until max hours (column G) needs to be coloured yellow on the matrix.

The time entered as end of shift (column H) needs to be highlighted as red with a "u" (diamond shape in wingdings).

Some times the tasks wrap around days (ie goes past midnight into the next day) and this may cause extra difficulty in the code, so if need be I can try to alter the matrix somehow to save on this extra coding. (not sure how I would be able to achieve this and still have it in the same format though).

There are also some blank cells in the columns (E,F,G,H). ie. between different days etc.

The code would need to fire everytime a time was changed in the columns, which in turn would mean it would first have to clear all the matrix to clear out the old colours.


Hoping someone is able to assist

Thanks in advance
Koala

[Jacob Hilderbrand]

We can do this with Conditional Formatting.

Select the range of cells. Make sure J5 is the active cell.

Format | Conditional Formatting...


Condition 1:

Formula Is =AND(ROUND($H5,4)<ROUND(K$3,4),ROUND($H5,4)>ROUND(I$3,4))
Format Red Fill Color, Black Font Color

Condition 2

Formula Is =AND($F5<=J$3,$G5>=J$3)
Format Yellow Fill Color, Yellow Font Color

Condition 3

Formula Is =AND($E5<=J$3,$F5>=J$3)
Format Blue Fill Color, Blue Font Color

Format the cells with Wingdings Font and White Font Color and put a u in each cell.

[Jacob Hilderbrand]

Here is your attachment with the changes.

[koala]

DRJ

Thanks for your reply

It works exactly as I need it to.

I have been unable to get on to this site for days as it keeps coming up as unavailable. I can get to the home page, but not the forum or any other pages.

I am a bit confused on one thing though.

I subscribed to the thread so I would get notification when someone posted.

The email is different to your post!!! I would have thought it was a replica.

I tried the response as per the email and it wasnt successful.

Here is the email I recieved

*********
Hello koala,

DRJ has just replied to a thread you have subscribed to entitled - Colour Cells
depending on time - in the Excel Help forum of VBA Express Forum.

This thread is located at:
http://www.vbaexpress.com/forum/showthread.php?t=1076&goto=newpost

Here is the message that has just been posted:
***************
We can do this with Conditional Formatting.

Select the range of cells.

Format | Conditional Formatting...

Condition 1:

Formula Is =AND(ROUND($H12,4)<ROUND(AK$3,4),ROUND($H12,4)>ROUND(AI$3,4))
Format Red Fill, Black Font

Condition 2

Formula Is =AND($F12<=AJ$3,$G12>=AJ$3)
Format Yellow Fill, Yellow Font

Condition 3

Formula Is =AND($E12<=AJ$3,$F12>=AJ$3)
Format Blue Fill, Blue Font

Format the cells with Wingdings Font and put a u in each cell.
***************


There may be other replies also, but you will not receive any more notifications
until you visit the forum again.

Yours,
The Team of Coders at VBA Express Forum

******************

As you can see the conditions refer to different cells than your post.

Also missing was the bit about J5 as active cell and the white u in the cells.

Anyway, that aside, it works great and thank you so much for your assistance

cheers
Koala

[Jacob Hilderbrand]

You're Welcome

The email is different because I editted to post. When I first posted it, I didn't realize that I copied the conditions from a different cell than J5 so it would be hard for you to line it up to the correct cells.

So I fixed it, but the email had already been sent.

Take Care