Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting color | Excel Worksheet Functions | |||
Color formatting | Excel Worksheet Functions | |||
Color Formatting | Excel Discussion (Misc queries) | |||
Conditional color formatting entries have wild color. | Excel Discussion (Misc queries) | |||
Color Formatting | Excel Programming |