Colour a row, based on a cell value

[Duncs]

Hi folks.

I have a spreadsheet that contains text, values and dates between
columns 'B' and 'I'. What I'd like to do is have formatting set
somewhere, so that if the value in the date column is more than two
weeks older than the date the file is opened, it should colour the
text from column 'B' to 'I' on the row red.

If the date is between 1 and 2 weeks in the past, it should colour the
text orange. If it is within 7 days old, it should colour it green.
If the date field is blank, the text colour should be black.

I don't fancy doing this via condiitonal formatting, as I'm thinking
you'd have to put this in each cell. Also, as I'm using Excel 2003,
you can only have 3 condiitonal formats...or would this be enough?
However, as I say, I'm not keen on having to put this formatting in
each cell.

Anyone know of a way to do this?

Duncs

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.CF.html#due for a worked example

HTH

Bob

 

[chg]

Sub Color_Dates()
Dim rWhere As Range
Dim rCell As Range
Set rWhere = Range("B1:B100")

For Each rCell In rWhere
Select Case rCell.Value
Case Is <= Date - 14
rCell.Interior.Color = 192 'red
Case Is <= Date - 7
rCell.Interior.Color = 49407 'orange
Case Is <= Date
rCell.Interior.Color = 5296274 'green
End Select
Next

End Sub

 

[Duncs]

Bob,

thanks for your reply. I'm looking at the info provided here:

http://www.xldynamic.com/source/xld.CF.html#lights

However, I'm having some logic problems!

If I base my date calculations on Today being 20/01/10 then...

20/01/10
<<green>>
13/01/10
<<orange>>
06/01/10
<<red>>

Any date after the 13/01/10 should be coloured green.
Any date after the 06/01/10, but before or eqaul to 13/01/10, should
be coloured orange.
Any date before or equal to 06/01/10, should be coloured red.

Given the above, the following should be true:

14/01/10 & 17/01/10 - Green
10/01/10 & 13/01/10 - Orange
04/01/10 & 06/01/10 - Red

To achieve this, I have the following conditional formatting and
results:

Green -
D6 contains the date 17/01/10
=AND(D6<>"",D6<TODAY()-7)

Orange -
D7 contains 07/01/10
=AND(D7<>"", D7<TODAY()-14, D7<=TODAY()-14)

Red -
D8 contains 01/01/10
=AND(D8<>"", D8>TODAY()-14)

However, what should be coloured Green, shows as Red and what should
be Orange & Red, show as Green!!

I'm guessing that there is a problem with my data logic, but I can't
figure it out.

Help!!

Many TIA.

Duncs

 

[Bob Phillips]

Condition 1: =AND(D6<>"", D6<=TODAY()-14) - red

Condition 2: =AND(D6<>"", D6<=TODAY()-7, D6>TODAY()-14) - amber

Condition 3: =AND(D6<>"", D6>TODAY()-7) - green

HTH

Bob


Bob,

thanks for your reply. I'm looking at the info provided here:

http://www.xldynamic.com/source/xld.CF.html#lights

However, I'm having some logic problems!

If I base my date calculations on Today being 20/01/10 then...

20/01/10
<<green>>
13/01/10
<<orange>>
06/01/10
<<red>>

Any date after the 13/01/10 should be coloured green.
Any date after the 06/01/10, but before or eqaul to 13/01/10, should
be coloured orange.
Any date before or equal to 06/01/10, should be coloured red.

Given the above, the following should be true:

14/01/10 & 17/01/10 - Green
10/01/10 & 13/01/10 - Orange
04/01/10 & 06/01/10 - Red

To achieve this, I have the following conditional formatting and
results:

Green -
D6 contains the date 17/01/10
=AND(D6<>"",D6<TODAY()-7)

Orange -
D7 contains 07/01/10
=AND(D7<>"", D7<TODAY()-14, D7<=TODAY()-14)

Red -
D8 contains 01/01/10
=AND(D8<>"", D8>TODAY()-14)

However, what should be coloured Green, shows as Red and what should
be Orange & Red, show as Green!!

I'm guessing that there is a problem with my data logic, but I can't
figure it out.

Help!!

Many TIA.

Duncs

 

[Duncs]

Bob,

Thanks for your reply. It works a treat. I can see where I was going
wrong, and I guessed it wouold be something simple.

Many thanks

Duncs

 

[Duncs]

Just one other thing...supposing that I wanted to colour in the cells
from column B to G, based on the value held in cell G(row num), how
would I do this?

I tried creating a new 'Range' variable and using this to assign the
row range of cells that I want to be filled in however, I can't figure
a way of assigning a row range, for example B5:H5, prior to colouring
in the cells.

Anyone help?

Duncs

 

[Gord Dibben]

Fill color in B to H based on date in B?

Sub Color_Dates()
Dim rWhere As Range
Dim rCell As Range
Dim rcols As Range
Dim n As Long
Set rWhere = Range("B1:B100")
For Each rCell In rWhere
n = rCell.Row
Set rcols = Range("B" & n & ":H" & n)
Select Case rCell.Value
Case Is <= Date - 14
rcols.Interior.Color = 192 'red
Case Is <= Date - 7
rcols.Interior.Color = 49407 'orange
Case Is <= Date
rcols.Interior.Color = 5296274 'green
End Select
Next rCell
End Sub


Gord Dibben MS Excel MVP

 

[Duncs]

Thanks Gord

Duncs

Fill color in B to H based on date in B?

Sub Color_Dates()
    Dim rWhere As Range
    Dim rCell As Range
    Dim rcols As Range
    Dim n As Long
    Set rWhere = Range("B1:B100")
    For Each rCell In rWhere
        n = rCell.Row
        Set rcols = Range("B" & n & ":H" & n)
        Select Case rCell.Value
        Case Is <= Date - 14
            rcols.Interior.Color = 192    'red
        Case Is <= Date - 7
            rcols.Interior.Color = 49407    'orange
        Case Is <= Date
            rcols.Interior.Color = 5296274    'green
        End Select
    Next rCell
End Sub

Gord Dibben  MS Excel MVP





- Show quoted text -