Excel VBA code to automatically change shape colour based on time.

[afzal_u1]

Hi,

I have working on pretty much the same problem here but with some changes.

I am total new to excel vba and I am working on a project in which in which I have to change the shape colour based on the time of the day for example if it is midnight the shape color should be yellow and if it is 6 O'Clock in the morning the shape colour should be green etc.

The number of shape that I have to work with are about 14 and I have been trying my level best to figure out how to put the time interval in the vba code so that it should work but to not avail.

I looked into attaching the excel sheet with my thread but there was no option so I am adding a part of the code that I want to make changes to currently the shape colour change but not with respect to time but I want to merge the time into the if loop so that time can control the colour of the shapes while other will only display what is the value of the shape.

This is some part of the code I want to merge time with.
[ If ss1 = 582693 Or ss2 = 582693 Or ss3 = 582693 Or ss4 = 582693 Or ss5 = 582693 Or ss6 = 582693 Or ss7 = 582693 Or ss8 = 582693 Then

's/s 582693
ActiveSheet.Shapes.Range(Array("Oval 19")).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0.5
.Solid
End With

Else
's/s 582693
ActiveSheet.Shapes.Range(Array("Oval 19")).Select
With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 255, 0)
.Transparency = 0.8
.Solid
End With

End If ]


If anyone can help me with this i would appreciate it a lot.

Thank you, <!-- END TEMPLATE: newreply_reviewbit -->

 

[EPURON]

So your asking: I would like a automated colour change on a spreadsheet using realtime? >_<

 

[afzal_u1]

Well long story short yes its pretty much what you said

But I dont want it to be synchronised with my computer clock but a separate column in the excel sheet in which I have defined time with half an hour interval for the whole day.

Hope this helps you to understand my question

 

[EPURON]

well i can do like: you have a inputbox asking what time it is , you add the time then it will start with the colour that i have set for the time then it will continue on the ticks that have started at the time you had entered and idk if u want it to loop. If you want it to loop reply back. It might take a few hours to code it because this seems like a somewhat a project. When is this due?

 

[afzal_u1]

Well I want it to loop but can you tell a bit more in details about the inputbox option that you mentioned. yea it is project and is due in next week but i have presentation tomorrow to show my progress with the project.

 

[EPURON]

Alright well i will see if i can do it, If not then i will try to get a friend to help you, Hes better then me :P

 

[EPURON]

Well with the inputbox, You can set the current time or anytime you would like and it would set the colour. With that time, it will start at the ticks at the time you added into the inputbox and it will loop. Thing is, if you turn off your computer then the code wouldn't be running and there wouldn't really be a point in looping the code but if u want to do that then i have no issue with that

 

[afzal_u1]

Do you know how I can attach my excel workbook with this thread. I believe it will help you with coding.

 

[EPURON]

upload it to mediafire and private message me the download link