Colour a row, based on a cell value
 

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

Colour a row, based on a cell value
 

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

HTH

Bob

"Duncs" wrote in message
...
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

Colour a row, based on a cell value
 

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

Colour a row, based on a cell value
 

Bob,

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

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<"", D8TODAY()-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


On Jan 18, 11:29*am, "Bob Phillips"
wrote:
Seehttp://www.xldynamic.com/source/xld.CF.html#duefor a worked example

HTH

Bob

"Duncs" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Colour a row, based on a cell value
 

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

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

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

HTH

Bob


"Duncs" wrote in message
...
Bob,

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

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<"", D8TODAY()-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


On Jan 18, 11:29 am, "Bob Phillips"
wrote:
Seehttp://www.xldynamic.com/source/xld.CF.html#duefor a worked example

HTH

Bob

"Duncs" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Colour a row, based on a cell value
 

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


On Jan 20, 2:37*pm, "Bob Phillips" wrote:
Condition 1: =AND(D6<"", D6<=TODAY()-14) - red

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

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

HTH

Bob

"Duncs" wrote in message

...
Bob,

thanks for your reply. *I'm looking at the info provided he

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<"", D8TODAY()-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

On Jan 18, 11:29 am, "Bob Phillips"
wrote:



Seehttp://www.xldynamic.com/source/xld.CF.html#duefora worked example

HTH

Bob

"Duncs" wrote in message

....

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Colour a row, based on a cell value
 

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


On Jan 18, 11:37*am, chg wrote:
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

Colour a row, based on a cell value
 

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

On Wed, 27 Jan 2010 01:19:50 -0800 (PST), Duncs
wrote:

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


On Jan 18, 11:37*am, chg wrote:
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

Colour a row, based on a cell value
 

Thanks Gord

Duncs

On Jan 27, 7:11*pm, Gord Dibben <gorddibbATshawDOTca wrote:
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

On Wed, 27 Jan 2010 01:19:50 -0800 (PST), Duncs
wrote:



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

On Jan 18, 11:37*am, chg wrote:
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- Hide quoted text -

- Show quoted text -