Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditioning formatting
I've used the 3 conditioning formats but would like to be able to add a
couple more. What i would like to be able to do is when i enter a letter or number into a cell it changes colour but still shows the letter or number. Is there a formula that allows me to do this or do i need to use a macro if so what do i need to do. All help is greatly appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditioning formatting
Here is an example
'----------------------------------------------------------------- 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 1: .Interior.ColorIndex = 3 'red Case 2: .Interior.ColorIndex = 6 'yellow Case 3: .Interior.ColorIndex = 5 'blue Case 4: .Interior.ColorIndex = 10 'green 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 (change the xxxx to gmail if mailing direct) "louiscourtney" wrote in message ... I've used the 3 conditioning formats but would like to be able to add a couple more. What i would like to be able to do is when i enter a letter or number into a cell it changes colour but still shows the letter or number. Is there a formula that allows me to do this or do i need to use a macro if so what do i need to do. All help is greatly appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditioning formatting
Bob what you have put down looks perfect for what I'm after but when i copy
and paste into the excel sheet it comes up with error the area i want it to work is D4:AH98 I would also like to when i enter the following letters the cell changes coulour accordingly H = Green S = Red L = Blue P = Pink Any ideas please "Bob Phillips" wrote: Here is an example '----------------------------------------------------------------- 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 1: .Interior.ColorIndex = 3 'red Case 2: .Interior.ColorIndex = 6 'yellow Case 3: .Interior.ColorIndex = 5 'blue Case 4: .Interior.ColorIndex = 10 'green 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 (change the xxxx to gmail if mailing direct) "louiscourtney" wrote in message ... I've used the 3 conditioning formats but would like to be able to add a couple more. What i would like to be able to do is when i enter a letter or number into a cell it changes colour but still shows the letter or number. Is there a formula that allows me to do this or do i need to use a macro if so what do i need to do. All help is greatly appreciated |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditioning formatting
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "D4:AH98" 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 "S": .Interior.ColorIndex = 3 'red Case "P": .Interior.ColorIndex = 7 'pink Case "L": .Interior.ColorIndex = 5 'blue Case "H": .Interior.ColorIndex = 10 'green 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 (change the xxxx to gmail if mailing direct) "louiscourtney" wrote in message ... Bob what you have put down looks perfect for what I'm after but when i copy and paste into the excel sheet it comes up with error the area i want it to work is D4:AH98 I would also like to when i enter the following letters the cell changes coulour accordingly H = Green S = Red L = Blue P = Pink Any ideas please "Bob Phillips" wrote: Here is an example '----------------------------------------------------------------- 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 1: .Interior.ColorIndex = 3 'red Case 2: .Interior.ColorIndex = 6 'yellow Case 3: .Interior.ColorIndex = 5 'blue Case 4: .Interior.ColorIndex = 10 'green 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 (change the xxxx to gmail if mailing direct) "louiscourtney" wrote in message ... I've used the 3 conditioning formats but would like to be able to add a couple more. What i would like to be able to do is when i enter a letter or number into a cell it changes colour but still shows the letter or number. Is there a formula that allows me to do this or do i need to use a macro if so what do i need to do. All help is greatly appreciated |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditioning formatting
Thank you Bob
Your a genius "Bob Phillips" wrote: '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "D4:AH98" 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 "S": .Interior.ColorIndex = 3 'red Case "P": .Interior.ColorIndex = 7 'pink Case "L": .Interior.ColorIndex = 5 'blue Case "H": .Interior.ColorIndex = 10 'green 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 (change the xxxx to gmail if mailing direct) "louiscourtney" wrote in message ... Bob what you have put down looks perfect for what I'm after but when i copy and paste into the excel sheet it comes up with error the area i want it to work is D4:AH98 I would also like to when i enter the following letters the cell changes coulour accordingly H = Green S = Red L = Blue P = Pink Any ideas please "Bob Phillips" wrote: Here is an example '----------------------------------------------------------------- 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 1: .Interior.ColorIndex = 3 'red Case 2: .Interior.ColorIndex = 6 'yellow Case 3: .Interior.ColorIndex = 5 'blue Case 4: .Interior.ColorIndex = 10 'green 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 (change the xxxx to gmail if mailing direct) "louiscourtney" wrote in message ... I've used the 3 conditioning formats but would like to be able to add a couple more. What i would like to be able to do is when i enter a letter or number into a cell it changes colour but still shows the letter or number. Is there a formula that allows me to do this or do i need to use a macro if so what do i need to do. All help is greatly appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deferring conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional Formatting: "handwritten" circles? | Excel Discussion (Misc queries) | |||
Paste Link - retaining formatting | Excel Discussion (Misc queries) | |||
conditional formatting with formula | Excel Worksheet Functions | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |