Color formatting with VBA
 

try somehting like:

Sub Shades()
worksheets("Sheet1").Select <======= Change this to suit
Dim cell As Range
Dim PSP As Worksheet
Set PSP = worksheets("Sheet1") <==== This too
For Each cell In PSP.Range("c8:r30") <==== Also this

If cell.Value < "" Then
If (cell.Value - Date) < 1 Then <===== Option 1(modify to suit)
With cell.Interior
.ColorIndex = 38 <===== Option 1(modify to suit)
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) < 14 Then <===== Option 2(modify to suit)
With cell.Interior
.ColorIndex = 36 <===== Option 2(modify to suit)
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) < 30 Then <===== Option 3(modify to suit)
With cell.Interior
.ColorIndex = 35 <===== Option 3(modify to suit)
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) 1 Then <===== Option 4(modify to suit)
With cell.Interior
.ColorIndex = 2 <===== Option 4(modify to suit) ADD AS MANY AS
REQUIRED
.Pattern = xlSolid
End With
End If
End If
Next
End Sub

--
Regards

Corey

Color formatting with VBA
 

A project for my next weekend. Many thanks for your help, Corey.

Brian

"Corey" wrote in message
...
try somehting like:

Sub Shades()
worksheets("Sheet1").Select <======= Change this to suit
Dim cell As Range
Dim PSP As Worksheet
Set PSP = worksheets("Sheet1") <==== This too
For Each cell In PSP.Range("c8:r30") <==== Also this

If cell.Value < "" Then
If (cell.Value - Date) < 1 Then <===== Option 1(modify to suit)
With cell.Interior
.ColorIndex = 38 <===== Option 1(modify to suit)
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) < 14 Then <===== Option 2(modify to suit)
With cell.Interior
.ColorIndex = 36 <===== Option 2(modify to suit)
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) < 30 Then <===== Option 3(modify to suit)
With cell.Interior
.ColorIndex = 35 <===== Option 3(modify to suit)
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) 1 Then <===== Option 4(modify to suit)
With cell.Interior
.ColorIndex = 2 <===== Option 4(modify to suit) ADD AS MANY AS
REQUIRED
.Pattern = xlSolid
End With
End If
End If
Next
End Sub

--
Regards

Corey

Color formatting with VBA
 

Managed to find some spare moments. It is now working fine. Thanks for your
well guided instructions. Just one question. Is there a simple way to modify
it so that it can be made available for use at various other workbook
locations?

TIA
Brian

"Corey" wrote in message
...
try somehting like:

Sub Shades()
worksheets("Sheet1").Select <======= Change this to suit
Dim cell As Range
Dim PSP As Worksheet
Set PSP = worksheets("Sheet1") <==== This too
For Each cell In PSP.Range("c8:r30") <==== Also this

If cell.Value < "" Then
If (cell.Value - Date) < 1 Then <===== Option 1(modify to suit)
With cell.Interior
.ColorIndex = 38 <===== Option 1(modify to suit)
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) < 14 Then <===== Option 2(modify to suit)
With cell.Interior
.ColorIndex = 36 <===== Option 2(modify to suit)
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) < 30 Then <===== Option 3(modify to suit)
With cell.Interior
.ColorIndex = 35 <===== Option 3(modify to suit)
.Pattern = xlSolid
End With
ElseIf (cell.Value - Date) 1 Then <===== Option 4(modify to suit)
With cell.Interior
.ColorIndex = 2 <===== Option 4(modify to suit) ADD AS MANY AS
REQUIRED
.Pattern = xlSolid
End With
End If
End If
Next
End Sub

--
Regards

Corey