vba to add colours to cells

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

thanks Rgonzo but this is part of a bigger Macro and I need the VBA to be able to colour the cells.

Also The cells to be coloured are not in column I they are in column L. The 'days' are in I

Regards

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

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

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?

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

Select allOpen in new window

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

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

sorry to be a nuicance
colour.xlsm

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

do you want me to color I as well where L is colored?

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!!

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

So is it a different metric that is being measured?

Hi Rob yes it is but they wanted to see a consolidate view on the one sheet.

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

Such an excellent Expert. Thannks