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?
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.