How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Zabo04 Your Own Question

Zabo04
Zabo04, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 283
Experience:  Experienced in Word, Excel, Access, Powerpoint, and Outlook.
58597962
Type Your Microsoft Office Question Here...
Zabo04 is online now
A new question is answered every 9 seconds

Dear SirsI am working with Excel 2003. In a worksheet, i

This answer was rated:

Dear Sirs
I am working with Excel 2003. In a worksheet, i have a date to cell A1 and another date
to cell B1. In cell D1, with formula, i have the number of years, months and days between
these dates.
For example:

A1 B1 D1
01/10/2013 01/11/2013 0 YEARS, 1 MONTHS, 0 DAYS

A2 B2 D2
01/09/2013 15/12/2013 0 YEARS, 3 MONTHS, 15 DAYS

A3 B3 D3
17/08/2009 12/03/2013 3 YEARS, 6 MONTHS, 23 DAYS

My problem is that i want to change the color of the cells in column D when the number
of the years is changing.
For example: for 1 years to 3 years, red color,
for 4 years to 6 years, green color,
for 7 years to 9 years, blue color,
for 9 years to 12 years, yellow color,
and so on.
I am trying to do that through VBA, because conditional formatting option has only 3
conditions as maximum.
How i can accomplish that?

Thanks In Advance

Zabo04 :

could you paste the excel file in this question, using the paperclip, which says for images, but will work for excel files

Zabo04 :

In vba we need to loop through the filled cells in your difference column, then set the color according to an if then statement

Zabo04 :

I have it

Zabo04 :

have you ever used VBA before?

Zabo04 :

and what version of excel do you have?

Customer:

Yes, i am working with VBA excel,

Customer:

My excel is 2003 version

Zabo04 :

okay

Zabo04 :

let me work on this for a few minutes

Customer:

Ok.

Zabo04 :

setting up the colors now

Zabo04 :

datediff isn't working as described, fixing that then it should be done

Zabo04 :

saving it and uploading now

Zabo04 :

click on and download the full size image

Zabo04 :

I commented the code so you should be able to make alterations as you see fit to change or add more conditions

Customer:

Just give 2-3 seconds to see the code.

Zabo04 :

no problem

Zabo04 :

apologize, you can delete this line: ActiveSheet.Range("E" & curRow).Value = timeBetween

Zabo04 :

i used it to debug o ensure I was getting the correct value for timeBetween

Customer:

Ok.

Customer:

I am changing the dates, but colors didn't change.

Zabo04 :

vba has to be rerun

Zabo04 :

it isn't like a formula that updates when you update the cells

Zabo04 :

if you rerun the vba it should change the colors

Customer:

it is possible to do this automatically, when change the value of the A columns?

Zabo04 :

you could

Zabo04 :

but you'd have the vba running and not be able to update the columns

Zabo04 :

formulas are automatic, vba is not

Zabo04 :

it's a limitation of excel

Zabo04 :

for that you'd need a custom application written in another language

Zabo04 :

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

Customer:

could you take a look to this xls? there is possible to do something like that?

Zabo04 :

write a continuously updating vba script?

Zabo04 :

it will be an infinite loop, so you will have to manually kill the macro

Zabo04 :

and windows may detect it as a crash

Zabo04 :

but it sometimes works

Customer:

can i upload another file?

Zabo04 :

yes

Customer:

Private Sub Worksheet_Change(ByVal Target As Range)
Set colorchanger = Range("D1:D10")
For Each Cell In colorchanger


If Cell.Value = 1 Then
Cell.Interior.ColorIndex = 7
End If
If Cell.Value = 2 Then
Cell.Interior.ColorIndex = 6
End If
If Cell.Value = 3 Then
Cell.Interior.ColorIndex = 5
End If
If Cell.Value = 4 Then
Cell.Interior.ColorIndex = 4
End If


If Cell.Value <> 1 And Cell.Value <> 2 And Cell.Value <> 3 And Cell.Value <> 4 Then
Cell.Interior.ColorIndex = xlNone
End If


Next
End Sub

Customer:

the system don't let me to upload another file, so i wrote the code.

Zabo04 :

odd

Customer:

this code change the color automatically when change the value of the cell.

Zabo04 :

okay

Zabo04 :

i see how

Zabo04 :

it used an event, I didn't think excel supported events in vba

Customer:

this works if you have only one value in the cell.

Customer:

the problem is that the cell contains values for days and month, which changing.

Customer:

i will try to customize your code.

Zabo04 :

inside of the function you pasted

Zabo04 :

comment out all of the lines of code

Zabo04 :

paste my function

Zabo04 :

had a problem with excel give me a second here

Zabo04 :

you can just put a call into the function I wrote

Zabo04 :

don't try that

Zabo04 :

it will become recursive

Zabo04 :

because a color change will cause another change outside of the event sub and it will recall itself

Customer:

just tried, and crashes.

Customer:

i mean the excel crashed.

Zabo04 :

events don't have recursion protection

Zabo04 :

it will lock and crash excel

Zabo04 :

once memory is full

Customer:

probably there is limitation of the excel.

Customer:

ok, there is no problem, i will try to customize your code.

Customer:

Thank you, XXXXX XXXXX time.

Zabo04 :

Got it

Zabo04 :

I apologize for being slow, my raid array had some timing issues that bogged my system down

Customer:

can i give single numbers instead RGB numbers to change the color?

Zabo04 :

yes

Zabo04 :

use interior.colorindex = whatever the number

Zabo04 :

of course you have to lookup the color indexes, but they are predefined that way

Zabo04 :

a google search should get it though

Customer:

Thank you

Customer:

Works like a charm!!!

Customer:

i placed the rate before. Did you receive it?

Zabo04 :

It says accepting but it doesn't show accepted, not sure why.

Customer:

do you want to rating again?

Zabo04 :

did you finish accepting the answer, that might be why it didn't go through

Customer:

if i rate again will be fix it, or i must do anything else?

Zabo04 :

can you ask a moderator, I don't want you to be charged twice and I'm not 100% sure what happened

Zabo04 :

you get charged for every accpet

Zabo04 :

accept

Customer:

the system does not leave to change webpage unless i place a rate, so i will rate again

Customer:

and if i charge twice, i will ask a refund from the moderator.

Zabo04 :

okay

Zabo04 :

that works

Zabo04 :

thank you for your patience

Customer:

did you receive it now?

Zabo04 :

I sent JustAnswer and e-mail to help with it

Zabo04 :

don't worry about it, they should take care of it, the conversation clearly shows you meant to accept once and only once

Zabo04 :

thank you

Customer:

ok, if there is any problem, i will happy to help.

Customer:

Your services was excellent!!! Thank you again.

Zabo04 and 2 other Microsoft Office Specialists are ready to help you
Customer: replied 3 years ago.

Dear Zabo04


 


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".


 


Thanks

It was rated and credited correctly. I had to shift the question from chat to Q&A for the process to finish. I had to email tech support for that help. Thanks again.