Color formatting with VBA

  • Thread starter Thread starter brian
  • Start date Start date
B

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
 
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
 
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
 
Back
Top