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 -