Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i get more than 3 colours in conditional formatting
I have tried to download bob's add in but this comes up with an error.
I need a code to show different colours when I type R, A, C, I, RA. I have tried using the code written which seems to work fine for numbers but not text...HELP! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i get more than 3 colours in conditional formatting
As ALWAYS, post your code for comments
-- Don Guillett Microsoft MVP Excel SalesAid Software "Nicole" wrote in message ... I have tried to download bob's add in but this comes up with an error. I need a code to show different colours when I type R, A, C, I, RA. I have tried using the code written which seems to work fine for numbers but not text...HELP! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i get more than 3 colours in conditional formatting
'----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "R": .Interior.ColorIndex = 3 'red Case "A": .Interior.ColorIndex = 6 'yellow Case "C" .Interior.ColorIndex = 5 'blue Case "I": .Interior.ColorIndex = 10 'green 'etc. End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nicole" wrote in message ... I have tried to download bob's add in but this comes up with an error. I need a code to show different colours when I type R, A, C, I, RA. I have tried using the code written which seems to work fine for numbers but not text...HELP! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i get more than 3 colours in conditional formatting
Thanks Bob - this is great!
Just one more thing, if i want to change the colours how do I know which colour code it is? "Bob Phillips" wrote: '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "R": .Interior.ColorIndex = 3 'red Case "A": .Interior.ColorIndex = 6 'yellow Case "C" .Interior.ColorIndex = 5 'blue Case "I": .Interior.ColorIndex = 10 'green 'etc. End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nicole" wrote in message ... I have tried to download bob's add in but this comes up with an error. I need a code to show different colours when I type R, A, C, I, RA. I have tried using the code written which seems to work fine for numbers but not text...HELP! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i get more than 3 colours in conditional formatting
FYI:
xl2007 offers lots more. Nicole wrote: I have tried to download bob's add in but this comes up with an error. I need a code to show different colours when I type R, A, C, I, RA. I have tried using the code written which seems to work fine for numbers but not text...HELP! -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i get more than 3 colours in conditional formatting
I record a macro when I type "Blue" into a cell. Then I change the color to
blue. Then I type "Green" and change the color to green. And on and on... Then I stop recording the macro and look at the code. And I use the numbers I see in that recorded code. Nicole wrote: Thanks Bob - this is great! Just one more thing, if i want to change the colours how do I know which colour code it is? "Bob Phillips" wrote: '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "R": .Interior.ColorIndex = 3 'red Case "A": .Interior.ColorIndex = 6 'yellow Case "C" .Interior.ColorIndex = 5 'blue Case "I": .Interior.ColorIndex = 10 'green 'etc. End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nicole" wrote in message ... I have tried to download bob's add in but this comes up with an error. I need a code to show different colours when I type R, A, C, I, RA. I have tried using the code written which seems to work fine for numbers but not text...HELP! -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i get more than 3 colours in conditional formatting
Public Enum xlColorIndex xlCIBlack = 1 xlCIWhite = 2 xlCIRed = 3 xlCIBrightGreen = 4 xlCIBlue = 5 xlCIYellow = 6 xlCIPink = 7 xlCITurquoise = 8 xlCIDarkRed = 9 xlCIGreen = 10 xlCIDarkBlue = 11 xlCIDarkYellow = 12 xlCIViolet = 13 xlCITeal = 14 xlCIGray25 = 15 xlCIGray50 = 16 xlCIPeriwinkle = 17 xlCIPlum = 18 xlCIIvory = 19 xlCILightTurquoise = 20 xlCIDarkPurple = 21 xlCICoral = 22 xlCIOceanBlue = 23 xlCIIceBlue = 24 'xlCIDarkBlue = 25 'xlCIPink = 26 'xlCIYellow = 27 'xlCITurquoise = 28 'xlCIViolet = 29 'xlCIDarkRed = 30 'xlCITeal = 31 'xlCIBlue = 32 xlCISkyBlue = 33 xlCILightGreen = 35 xlCILightYellow = 36 xlCIPaleBlue = 37 xlCIRose = 38 xlCILavender = 39 xlCITan = 40 xlCILightBlue = 41 xlCIAqua = 42 xlCILime = 43 xlCIGold = 44 xlCILightOrange = 45 xlCIOrange = 46 xlCIBlueGray = 47 xlCIGray40 = 48 xlCIDarkTeal = 49 xlCISeaGreen = 50 xlCIDarkGreen = 51 xlCIBrown = 53 xlCIIndigo = 55 xlCIGray80 = 56 End Enum -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nicole" wrote in message ... Thanks Bob - this is great! Just one more thing, if i want to change the colours how do I know which colour code it is? "Bob Phillips" wrote: '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "R": .Interior.ColorIndex = 3 'red Case "A": .Interior.ColorIndex = 6 'yellow Case "C" .Interior.ColorIndex = 5 'blue Case "I": .Interior.ColorIndex = 10 'green 'etc. End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nicole" wrote in message ... I have tried to download bob's add in but this comes up with an error. I need a code to show different colours when I type R, A, C, I, RA. I have tried using the code written which seems to work fine for numbers but not text...HELP! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i get more than 3 colours in conditional formatting
Run this macro.
Sub ListColorIndexes() Dim Ndx As Long Sheets.Add For Ndx = 1 To 56 Cells(Ndx, 1).Interior.ColorIndex = Ndx Cells(Ndx, 2).Value = Hex(ThisWorkbook.Colors(Ndx)) Cells(Ndx, 3).Value = Ndx Next Ndx End Sub Gord Dibben MS Excel MVP On Thu, 24 Apr 2008 06:37:16 -0700, Nicole wrote: Thanks Bob - this is great! Just one more thing, if i want to change the colours how do I know which colour code it is? "Bob Phillips" wrote: '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "R": .Interior.ColorIndex = 3 'red Case "A": .Interior.ColorIndex = 6 'yellow Case "C" .Interior.ColorIndex = 5 'blue Case "I": .Interior.ColorIndex = 10 'green 'etc. End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Nicole" wrote in message ... I have tried to download bob's add in but this comes up with an error. I need a code to show different colours when I type R, A, C, I, RA. I have tried using the code written which seems to work fine for numbers but not text...HELP! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colours missing in conditional formatting. | Excel Worksheet Functions | |||
Conditional Formatting with Colours / Colors | Excel Worksheet Functions | |||
is it possible to have more than 3 colours in conditional format | Excel Worksheet Functions | |||
Create conditional IF to format cells using 6 different colours | Excel Worksheet Functions | |||
How do I set up conditional cell fill colours in Excel? | Excel Worksheet Functions |