Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting limits
Hello everyone, Currently I am working on a spreadsheet with the property that every cell can take the values 1 through 9. Now I would like these cell to automatically take another background color depending on these values (1=yellow ,2=green ,..., 9=black). My first idea was to solve this problem via conditional formatting, but apparently Excel does not allow more than 3 conditions, and I need 9 of them! Does anyone know a workaround this problem? Many thanks in advance! Lucas. -- Lucas Lehmer ------------------------------------------------------------------------ Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514 View this thread: http://www.excelforum.com/showthread...hreadid=542791 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting limits
Worksheet events. For example
'----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" 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 Phillips (remove xxx from email address if mailing direct) "Lucas Lehmer" wrote in message news:Lucas.Lehmer.27xuf3_1147846636.6559@excelforu m-nospam.com... Hello everyone, Currently I am working on a spreadsheet with the property that every cell can take the values 1 through 9. Now I would like these cell to automatically take another background color depending on these values (1=yellow ,2=green ,..., 9=black). My first idea was to solve this problem via conditional formatting, but apparently Excel does not allow more than 3 conditions, and I need 9 of them! Does anyone know a workaround this problem? Many thanks in advance! Lucas. -- Lucas Lehmer ------------------------------------------------------------------------ Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514 View this thread: http://www.excelforum.com/showthread...hreadid=542791 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting limits
Thank you, Bob! It really works! However, I still have a small problem... Let's say in cell A1 I can enter any value between 1 and 9. And cell A2 contains the formula "=A1". Well, in that case, if I enter 3 in cell A1, the cell A1 turns red, but A2 doesn't! How come? And is there a solution for this problem? -- Lucas Lehmer ------------------------------------------------------------------------ Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514 View this thread: http://www.excelforum.com/showthread...hreadid=542791 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting limits
Try this variation
Private Const WS_RANGE As String = "A1:A2" '----------------------------------------------------------------- Private Sub Worksheet_Calculate() '----------------------------------------------------------------- Dim cell As Range For Each cell In Me.Range(WS_RANGE) SetColour cell Next cell End Sub '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then SetColour Target End If ws_exit: Application.EnableEvents = True End Sub Private Sub SetColour(ByVal Target As Range) 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 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 Phillips (remove xxx from email address if mailing direct) "Lucas Lehmer" wrote in message news:Lucas.Lehmer.27y6wz_1147862704.9114@excelforu m-nospam.com... Thank you, Bob! It really works! However, I still have a small problem... Let's say in cell A1 I can enter any value between 1 and 9. And cell A2 contains the formula "=A1". Well, in that case, if I enter 3 in cell A1, the cell A1 turns red, but A2 doesn't! How come? And is there a solution for this problem? -- Lucas Lehmer ------------------------------------------------------------------------ Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514 View this thread: http://www.excelforum.com/showthread...hreadid=542791 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting limits
Thank you for the quick reply, Bob. But unfortunately, no luck! The effect is the same: only the VALUE is copied from A1 to A2, but not the FORMAT :-((( Did you try it yourself? And did it work? In that case I might me doing something wrong. Regards, Lucas. -- Lucas Lehmer ------------------------------------------------------------------------ Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514 View this thread: http://www.excelforum.com/showthread...hreadid=542791 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting limits
I did try it Lucas, but I am not sure what you mean by the value being
copied but not the format. My code doesn't copy anything, it just sets the background colour of those cells. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Lucas Lehmer" wrote in message news:Lucas.Lehmer.27y9xc_1147866601.9027@excelforu m-nospam.com... Thank you for the quick reply, Bob. But unfortunately, no luck! The effect is the same: only the VALUE is copied from A1 to A2, but not the FORMAT :-((( Did you try it yourself? And did it work? In that case I might me doing something wrong. Regards, Lucas. -- Lucas Lehmer ------------------------------------------------------------------------ Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514 View this thread: http://www.excelforum.com/showthread...hreadid=542791 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |