VBA Code to Change Cell Color Based On the Cell Date/Value (like conditional formatting)

hi buddy,

i want to have a code that can change the cell color automatically based on the cell date as follows:

cell range A1:D1, A10:D10, A20:D20

condition 1: if the cell value in the ranges above which is early than 1 Oct 13, then the cell color change to blue
condition 2: if the cell value in the ranges above which is between 1 Oct 13 and 31 Oct 13 both dates included, then the cell color change to red
condition 3: if the cell value in the ranges above which is between 1 Nov 13 and 30 Nov 13 both dates included, then the cell color change to yellow
condition 4: if the cell value in the ranges above which is between 1 Dec 13 and 12 Dec 13 both dates included, then the cell color change to orange
condition 5: if the cell value in the ranges above which is equal to "leakage", then the cell color change to back

many thanks!
roy

Originally Posted by davesexcel

Check out conditional formatting.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

thanks buddy,

but my office version is 2003, so need vba code for such function, would you help?

many thanks,
roy

yes, xl'03 has conditional formatting, but it can only 3 conditions, so that why i want to have to vba code.

i write a code below but don't why can't work perfectly:

Private Sub cellcolordate()

Dim cell As Range

For Each cell In Range("b9:ah10, b15:ak40, b52:ah53, b58:ak83")

If cell.Value >= 1 And cell.Value < "1/10/2013" Then _
cell.Interior.Color = vbBlue


If cell.Value >= "1/10/2013" And cell.value < "31/10/2013" Then _
cell.Interior.Color = vbRed

If cell.Value >= "1/11/2013" And cell.Value < "30/11/2013" Then _
cell.Interior.Color = vbYellow

If cell.Value >= "1/12/2013" And cell.Value < "31/12/2013" Then _
cell.Interior.COlor = vbOrange

If cell.Value = "leakage" Then _
cell.Interior.Color = vbBlack

End If

Next cell
End Sub