Gil
04-07-2013, 09:53 AM
Hello
I am trying to set up a workbook for different bills to be paid with an alert system for forthcoming dates. I have attached an example workbook that has conditional formatting set up on worksheet that I am happy with. What I need help with is the code on the worksheet tab to change colour after certain conditions are met.
For example there is a due date in C4 with D6 being plus 30 days. If D6 is greater than 30 then I want the tab to be green, less than 30 orange, -30 red. The code I am tinkering with seems to run through but does not change the tab colour. At present it is only the active cell that I have set but I would like it to be the range D6:D12. All help or comment would be appreciated
Sorry, I forgot to include the bit of code I was trying
Sub Auto_Open()
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook
For Each ws In wb.Worksheets
Range("D6").Select
If ActiveCell.FormulaR1C1 = "=Value>(RC[-1]+30)" Then
ActiveSheet.Tab.ColorIndex = 3
ElseIf ActiveCell.FormulaR1C1 = "=Value(RC[-1]+30)" Then
ActiveSheet.Tab.ColorIndex = 46
ElseIf ActiveCell.FormulaR1C1 = "=Value<(RC[-1]-30)" Then
ActiveSheet.Tab.ColorIndex = 4
End If
Next ws
End Sub
I am trying to set up a workbook for different bills to be paid with an alert system for forthcoming dates. I have attached an example workbook that has conditional formatting set up on worksheet that I am happy with. What I need help with is the code on the worksheet tab to change colour after certain conditions are met.
For example there is a due date in C4 with D6 being plus 30 days. If D6 is greater than 30 then I want the tab to be green, less than 30 orange, -30 red. The code I am tinkering with seems to run through but does not change the tab colour. At present it is only the active cell that I have set but I would like it to be the range D6:D12. All help or comment would be appreciated
Sorry, I forgot to include the bit of code I was trying
Sub Auto_Open()
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ThisWorkbook
For Each ws In wb.Worksheets
Range("D6").Select
If ActiveCell.FormulaR1C1 = "=Value>(RC[-1]+30)" Then
ActiveSheet.Tab.ColorIndex = 3
ElseIf ActiveCell.FormulaR1C1 = "=Value(RC[-1]+30)" Then
ActiveSheet.Tab.ColorIndex = 46
ElseIf ActiveCell.FormulaR1C1 = "=Value<(RC[-1]-30)" Then
ActiveSheet.Tab.ColorIndex = 4
End If
Next ws
End Sub