# 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

###### Who is Participating?

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.

Commented:
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
Author Commented:
thanks Rgonzo but this is part of a bigger Macro and I need the VBA to be able to colour the cells.
0
Author Commented:
Also The cells to be coloured are not in column I they are in column L. The 'days' are in I

Regards
0
Finance 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
Finance AnalystCommented:

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
Finance AnalystCommented:

jagwarman - can you provide a sample with more data?
0
Commented:
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
``````
0
Author 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
Commented:
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
``````
0

Experts Exchange Solution brought to you by

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

Author Commented:
Hi Rgonzo, it still only colours 'L' if the data is in there and 'I'

sorry to be a nuicance
colour.xlsm
0
Author 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
Commented:
do you want me to color I as well where L is colored?
0
Finance 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
Author 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
Finance AnalystCommented:
So is it a different metric that is being measured?
0
Author Commented:
Hi Rob yes it is but they wanted to see a consolidate view on the one sheet.
0
Finance 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
Author 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.