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: 214
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

Customer Question

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
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Zabo04 replied 1 year ago.

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, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 214
Experience: Experienced in Word, Excel, Access, Powerpoint, and Outlook.
Zabo04 and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year 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

Expert:  Zabo04 replied 1 year ago.
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.

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
Zabo04
Zabo04
Master's Student
37 Satisfied Customers
Experienced in Word, Excel, Access, Powerpoint, and Outlook.