Change tab colour when condition is met

[Gil]

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

[mdmackillop]

Best guess as to your requirements
Sub Auto_Open()

Dim ws As Worksheet
Dim wb As Workbook
Dim cel As Range, x As Long, y As Long

Set wb = ThisWorkbook

For Each ws In wb.Worksheets
x = 60
'Find minimum value
For Each cel In ws.Range("C6").Resize(7)
x = Application.Min(x, (cel.Offset(, 1) - cel))
Next

'Allocate colour
Select Case x
Case Is >= 30
ws.Tab.ColorIndex = 4
Case Is > 0
ws.Tab.ColorIndex = 46
Case Is < 0
ws.Tab.ColorIndex = 3
End Select
Next ws
End Sub

[Gil]

Thank you mdmckillop

The code you have supplied doesn't seem to work past the Case Is >=30. I have figured out what most bits of the code refer to but what does x=60 mean. Also when the WB is opened the code does not auto run.

Thanks for your continued support Gil

[mdmackillop]

Auto_Open goes into a standard module.

You say you want to inspect D6 :D12. That is what the Find Minimum does. To find a minimum, I need to start somewhere, so I set X to 60, a value bigger than those you are looking to compare.
The Select Case may need some fine tuning to suit your requirements, I'll leave that to you.