vba to add colours to cells

could an expert help me out with this one please.

In the active cell, I need to add colours if

I2="1-10 Days" colour cell Green
I2="10-20 Days"  colour cell Amber
I2="> 20 Days"   colour cell Red
I2="0-1 Days"  colour cell Green
I2="2-5 Days"   colour cell Amber
I2=">5 Days"   colour cell Red

Thanks in advance
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

you could use these formulas in the conditional formatting

=(I2=">20 Days")+(I2=">5 Days")
=(I2="10-20 Days")+(I2="2-5 Days")
=(I2="1-10 Days")+(I2="0-1 Days")


Regards
EE20150306.xlsx
0
JagwarmanAuthor Commented:
thanks Rgonzo but this is part of a bigger Macro and I need the VBA to be able to colour the cells.
0
JagwarmanAuthor Commented:
Also The cells to be coloured are not in column I they are in column L. The 'days' are in I

Regards
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Rob HensonFinance AnalystCommented:
Once the Conditional formatting is in place, as the values change with the VBA the colours will change accordingly.

Can you upload a workbook with some sample data, more than just one line as in file uploaded?

Thanks
Rob
0
Rob HensonFinance AnalystCommented:
Your question has some contradiction:

I2="1-10 Days" colour cell Green
I2="10-20 Days"  colour cell Amber
I2="> 20 Days"   colour cell Red
I2="0-1 Days"  colour cell Green  ~ this is within the 1 - 10 so still Green
I2="2-5 Days"   colour cell Amber  ~ this is within the 1 - 10 so would be Green
I2=">5 Days"   colour cell Red  ~ this is within the 1 - 10 so would be Green

Or should the cell references at the beginning of the second block be different?

Thanks
Rob
0
Rob HensonFinance AnalystCommented:
Apologies to rgonzo about the comment about the uploaded file, just noticed that it was actually you that uploaded.

jagwarman - can you provide a sample with more data?
0
Rgonzo1971Commented:
Hi,

pls try

Sub macro()

For Each c In Range(Range("L1"), Range("L" & Cells.Rows.Count).End(xlUp))
    Select Case c.Offset(0, -3).Value
        Case ">20 Days", ">5 Days"
            c.Interior.Color = vbRed
        Case "10-20 Days", "2-5 Days"
            c.Interior.Color = 49407
        Case "1-10 Days", "0-1 Days"
            c.Interior.Color = vbGreen
        End Select
Next
End Sub

Open in new window

0
JagwarmanAuthor Commented:
Hi Rgonzo,

Sorry it's nearly there but not quite. It only colours in L if both I and L have the data in them.

So if 'I' has >20 Days and 'L' has >20 Days L goes Red

If 'I' has >20 Days and 'L' is blank L does not go Red.

It is this second scenario I need L to go Red

Thanks
0
Rgonzo1971Commented:
HI,

pls try

Sub macro()

For Each c In Range(Range("L1"), Range("L" & Cells.Rows.Count).End(xlUp))
    Select Case c.Offset(0, -3).Value & c.Value
        Case ">20 Days>20 Days", ">5 Days>5 Days"
            c.Interior.Color = vbRed
        Case "10-20 Days10-20 Days", "2-5 Days2-5 Days"
            c.Interior.Color = 49407
        Case "1-10 Days1-10 Days", "0-1 Days0-1 Days"
            c.Interior.Color = vbGreen
        End Select
Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Hi Rgonzo, it still only colours 'L' if the data is in there and 'I'

sorry to be a nuicance
colour.xlsm
0
JagwarmanAuthor Commented:
Hi Rob, there is no contradiction and as Rgonzo has shown in his file it does work, but, I need this to be done using VBA

Regards
0
Rgonzo1971Commented:
do you want me to color I as well where L is colored?
0
Rob HensonFinance AnalystCommented:
So how do you justify "2 - 5 Days" and "10 - 20 Days" being Amber?

I am assuming it is number of days for processing or receipt of something or something of that ilk. If it is done within "2 - 5 Days" then it gets Amber rating but if the service instead takes 10 - 20 Days it also gets Amber rating. How do the service providers get incentive to complete within 2 - 5 Days when they know they will get same rating even if they take up to 20 days.

Confused!!
0
JagwarmanAuthor Commented:
When I was asked to build a Dashboard for these guys I had the same thoughts. However, it works because the original data is on a different sheet. So lets say:

in Sheet 1 items show up as 12 so they would be classified as 10-20 Days

In Sheet 2 the criteria is different so an item 3 days would be 2-5 Days

When I pull this into the Dashboard if cell I2 has either 10-20 Days or 2-5 Days then it is Amber.

take a look at Rgonzo's file
0
Rob HensonFinance AnalystCommented:
So is it a different metric that is being measured?
0
JagwarmanAuthor Commented:
Hi Rob yes it is but they wanted to see a consolidate view on the one sheet.
0
Rob HensonFinance AnalystCommented:
OK, if thats what is wanted then so be it. I am here but to supply the means of doing so and not question why.

So the Conditional Formatting can be applied to column I & L as suggested by Rgonzo's original formula based CF and as and when the data changes, the formatting will change.

Thanks
Rob H
0
JagwarmanAuthor Commented:
Such an excellent Expert. Thannks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.