Color formatting based on date in another cell with VB

I have been trying to get a column of cells that have a date assigned to then to change colors based on a date in another cell.
I cannot use conditional formatting because the cells are populated with a macro that I cannot change and has the color formats included in the macro.
I want the macro to run whenever the worksheet is opened. this would then overwrite the color formats that the other macro put in.

The "master date" is in C1, the column of cells is in the range C8:C116.
I need 2 colors. Yellow if the date in C1 is 1 to 3 days later than the dates in the column. Red if the date in C1 is the same or earlier than the dates in the column.
I'm a newbie at VB and any help would be greatly appreaciated.

Thank You

Hello
The following code should work placed in the ThisWorkbook module, Workbook Open event. Assuming you're referencing Sheet1.

Dim c As Range
With Worksheets("Sheet1")
    For Each c In Range("C8:C116")
        Select Case c.Value
            Case Is <= Range("C1")
                c.Interior.Color = vbRed
            Case Is < Range("C1") + 4
                c.Interior.Color = vbYellow
        End Select
    Next c
End With

Hope this helps
DBY

Thank You,

I an getting the error "subscript out of range"
I changed the With Worksheets("Sheet1") to With Worksheets("Sheet17")that is my sheet number

I have alot going on in the Thisworkbook, would it be possible to make it work on the opening of the workshet?
that codepage is empty

Thank You

I don't have any problems in either 2003 or 2007. Seems to work fine.

If it makes a difference I am using Excel 2002 sp3

Thanks Again!

Not sure why it's not working on the Workbook open event but yes you can try the code on the Worksheet Activate event on Sheet17. The version shouldn't make any difference but I'll check.

DBY

Originally Posted by DBY

Not sure why it's not working on the Workbook open event but yes you can try the code on the Worksheet Activate event on Sheet17. The version shouldn't make any difference but I'll check.

DBY

I just moved it to the worksheet and I do not get any errors, however it does not do anything either

thank you

Also Ijust thought of something, it really needs to be on the worksheet open, this worksheet gets changed after the workbook is opened and the colors to be changed whenever the worksheet gets opened

Thank you

Could it be because the column has conditional formating set to it from the other macro that I cannot change that I mentioned in the post?

If there is conditional formatting already being applied to the cells then it might well affect the macro. Difficult to say without seeing the workbook what exactly is happening.

Would it be possible for the macro you sent to delete all conditional formatting, then apply he colors?
I may be able to send you the worksheet, but my boss will not let me send the whole workbook?

Thank You

Try the amended code:

Dim c As Range
With Worksheets("Sheet17")
    Range("C8:C116").FormatConditions.Delete
    For Each c In Range("C8:C116")
        Select Case c.Value
            Case Is <= Range("C1")
                c.Interior.Color = vbRed
            Case Is < Range("C1") + 4
                c.Interior.Color = vbYellow
        End Select
    Next c
End With

I changed it to this and it mostly works THANK YOU
The problems I have is I have to activate the sheet 2 times before it takes affect.
Any blank cells are red.
Last problem is is there a way so that if the dates does not meet the parameters for red or yellow that they would go back to white?

Thank You very much for doing this so fast!

Private Sub Worksheet_Activate()
Dim c As Range
'With Worksheets("Sheet17")
    Range("C8:C116").FormatConditions.DELETE
    For Each c In Range("C8:C116")
        Select Case c.Value
            Case Is <= Range("C1")
                c.Interior.Color = vbRed
            Case Is < Range("C1") + 4
                c.Interior.Color = vbYellow
        End Select
    Next c
'End With
End Sub

Not sure why the sheet has to be activated twice but a least you've got something working. Try this amended code:

Application.ScreenUpdating = False
Dim c As Range
With Worksheets("Sheet17")
    Range("C8:C116").FormatConditions.Delete
    For Each c In Range("C8:C116")
        Select Case c.Value
            Case Is = ""
                c.Interior.ColorIndex = xlNone
            Case Is <= Range("C1")
                c.Interior.Color = vbRed
            Case Is < Range("C1") + 4
                c.Interior.Color = vbYellow
        End Select
    Next c
End With

If the cells are blank they should now remain clear of a fill colour. I've also added the Screen updating line to make it a bit more efficient.

DBY

This doesn't change the colors at all

I changed to this and now the blanks are white.
What is weird is that once the color has been changed to yellow or red it will not change back to white

Thanks for all your help

Private Sub Worksheet_Activate()
Dim c As Range
'With Worksheets("Sheet17")
    Range("C8:C116").FormatConditions.DELETE
    For Each c In Range("C8:C116")
        Select Case c.Value
        Case Is = ""
            c.Interior.ColorIndex = xlNone
        Case Is <= Range("C1")
            c.Interior.Color = vbRed
        Case Is < Range("C1") + 4
            c.Interior.Color = vbYellow
        End Select
    Next c
'End With
End Sub

I'm at a loss to know what to suggest. It works perfectly on my PC. I've attached my example file for you to look at. See if this one is working on your PC. Remember if the Workbook is on Sheet 17 when it opens the macro won't run. It only runs when Sheet 17 is selected from another tab.

Maybe its my excel 2002, your example if I change the date after a color has been applied does not go back to white either.
I'm totally confused??

I just realized that if the date starts out white it will change to red or yellow, when changing a red date to a yellow date the color will turn to yellow. when trying to change this yellow date to a white date it will not change. This happens on your sheet also.

Strange

Macro doesn't work like conditional formatting on the sheet. You have to run it again by deactivating the sheet and reactivating.

I understand that. It does not work when changing sheets and reactivating. Eveything works except the color will not change back to white

I changed to this and now I THINK it is all working

Thank you

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Dim c As Range
With Worksheets("Sheet17")
    Range("C8:C116").FormatConditions.Delete
    For Each c In Range("C8:C116")
        Select Case c.Value
            Case Is = ""
                c.Interior.ColorIndex = xlNone
            Case Is <= Range("C1")
                c.Interior.Color = vbRed
            Case Is < Range("C1") + 4
                c.Interior.Color = vbYellow
                Case Is >= Range("C1")
                c.Interior.ColorIndex = xlNone
                
        End Select
    Next c
End With
Application.ScreenUpdating = True
End Sub

I think I'm getting what you're saying. Try this:

Application.ScreenUpdating = False
Dim c As Range
With Worksheets("Sheet17")
    .Range("C8:C116").FormatConditions.Delete
    .Range("C8:C116").Interior.ColorIndex = xlNone
    For Each c In Range("C8:C116")
        Select Case c.Value
            Case Is = ""
                c.Interior.ColorIndex = xlNone
            Case Is <= Range("C1")
                c.Interior.Color = vbRed
            Case Is < Range("C1") + 4
                c.Interior.Color = vbYellow
        End Select
    Next c
End With

Works Great!

Thank You for all your work

Glad we got there in the end.

Regards DBY

Hello, what you did is working good. Just wondering, would it be hard to make the columns B,D and E turn the same color as C?

Thank you

Hi
Glad it's working. I think if you amend the code to include the columns of B, D and E it should work.

Application.ScreenUpdating = False
Dim c As Range
With Worksheets("Sheet17")
    .Range("B8:E116").FormatConditions.Delete
    .Range("B8:E116").Interior.ColorIndex = xlNone
    For Each c In Range("B8:E116")
        Select Case c.Value
            Case Is = ""
                c.Interior.ColorIndex = xlNone
            Case Is <= Range("C1")
                c.Interior.Color = vbRed
            Case Is < Range("C1") + 4
                c.Interior.Color = vbYellow
        End Select
    Next c
End With

DBY

All this does is turn any used cell in column D red.
This is what I thought might work also but of coarse can't be that easy

Thank You

I think the problem is that my columns have different formats. My master date column is set to date and some of the other columns are text or numbers.
When I change the master date to a number format then some columns work.
Here is a stripped down sheet.

Thank You

Hello,
After looking at it more I think I need a way to say if a cell in column C is yellow or Red then cells in the same row in columns B,D,E and F are also yellow or red.
Thanks for all your help