Color formatting with VBA

[brian]

Excel menu allows me to conditionally colour formate a selected range, based
on cell values, up to 3 conditions - i.e. a choice of only 3 colours. Can I
colour formate cells that have value in steps of ten (1-10, 11-20,
.......81-90 and 91-100) using VBA? This is my preferred choice. If not can
it be done using coloured fonts? Thanks for any help.

TIA
Brian

 

[Corey]

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

 

[brian]

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

Brian

 

[brian]

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