Microsoft Office Questions? Ask a IT Expert for Support ASAP
could you paste the excel file in this question, using the paperclip, which says for images, but will work for excel files
In vba we need to loop through the filled cells in your difference column, then set the color according to an if then statement
I have it
have you ever used VBA before?
and what version of excel do you have?
Yes, i am working with VBA excel,
My excel is 2003 version
let me work on this for a few minutes
setting up the colors now
datediff isn't working as described, fixing that then it should be done
saving it and uploading now
click on and download the full size image
I commented the code so you should be able to make alterations as you see fit to change or add more conditions
Just give 2-3 seconds to see the code.
apologize, you can delete this line: ActiveSheet.Range("E" & curRow).Value = timeBetween
i used it to debug o ensure I was getting the correct value for timeBetween
I am changing the dates, but colors didn't change.
vba has to be rerun
it isn't like a formula that updates when you update the cells
if you rerun the vba it should change the colors
it is possible to do this automatically, when change the value of the A columns?
but you'd have the vba running and not be able to update the columns
formulas are automatic, vba is not
it's a limitation of excel
for that you'd need a custom application written in another language
for a vba you'd have it running, checking the clock continuously, but that means the vba code is running the whole time, so it locks you from editing the worksheet
could you take a look to this xls? there is possible to do something like that?
write a continuously updating vba script?
it will be an infinite loop, so you will have to manually kill the macro
and windows may detect it as a crash
but it sometimes works
can i upload another file?
Private Sub Worksheet_Change(ByVal Target As Range)Set colorchanger = Range("D1:D10")For Each Cell In colorchanger
If Cell.Value = 1 ThenCell.Interior.ColorIndex = 7End IfIf Cell.Value = 2 ThenCell.Interior.ColorIndex = 6End IfIf Cell.Value = 3 ThenCell.Interior.ColorIndex = 5End IfIf Cell.Value = 4 ThenCell.Interior.ColorIndex = 4End If
If Cell.Value <> 1 And Cell.Value <> 2 And Cell.Value <> 3 And Cell.Value <> 4 ThenCell.Interior.ColorIndex = xlNoneEnd If
the system don't let me to upload another file, so i wrote the code.
this code change the color automatically when change the value of the cell.
i see how
it used an event, I didn't think excel supported events in vba
this works if you have only one value in the cell.
the problem is that the cell contains values for days and month, which changing.
i will try to customize your code.
inside of the function you pasted
comment out all of the lines of code
paste my function
had a problem with excel give me a second here
you can just put a call into the function I wrote
don't try that
it will become recursive
because a color change will cause another change outside of the event sub and it will recall itself
just tried, and crashes.
i mean the excel crashed.
events don't have recursion protection
it will lock and crash excel
once memory is full
probably there is limitation of the excel.
ok, there is no problem, i will try to customize your code.
Thank you, XXXXX XXXXX time.
I apologize for being slow, my raid array had some timing issues that bogged my system down
can i give single numbers instead RGB numbers to change the color?
use interior.colorindex = whatever the number
of course you have to lookup the color indexes, but they are predefined that way
a google search should get it though
Works like a charm!!!
i placed the rate before. Did you receive it?
It says accepting but it doesn't show accepted, not sure why.
do you want to rating again?
did you finish accepting the answer, that might be why it didn't go through
if i rate again will be fix it, or i must do anything else?
can you ask a moderator, I don't want you to be charged twice and I'm not 100% sure what happened
you get charged for every accpet
the system does not leave to change webpage unless i place a rate, so i will rate again
and if i charge twice, i will ask a refund from the moderator.
thank you for your patience
did you receive it now?
I sent JustAnswer and e-mail to help with it
don't worry about it, they should take care of it, the conversation clearly shows you meant to accept once and only once
ok, if there is any problem, i will happy to help.
Your services was excellent!!! Thank you again.
I would like to ask about rating & credit issue, if resolved.
I can confirm again, that my question anwered 100%
and i rated your service as "Excellent service".