Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 conditional formats in excel
I have used the below code to get around the 3 conditional formats in excel.
But this only work when new values are entered. How do get this code to work for values which I have already entered. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("C4:IR30")) Is Nothing Then Select Case Target Case "C", "c" icolor = 8 'light blue Case "D", "D" icolor = 9 'brown Case "G", "g" icolor = 6 'yellow Case "H", "h" icolor = 3 'red Case "K", "k" icolor = 7 'pink Case "L", "l" icolor = 4 'green Case "O", "o" icolor = 20 'light blue Case "S", "s" icolor = 10 'dark green Case "C", "c" icolor = 8 'light blue Case "X", "x" icolor = 15 'grey Case Else 'Whatever '1 black, 2 white, 5 dark blue, 11 dark blue End Select Target.Interior.ColorIndex = icolor End If End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 conditional formats in excel
This should help
http://www.microsoft.com/office/comm...7-ee3abfdb66d9 "Manj" wrote: I have used the below code to get around the 3 conditional formats in excel. But this only work when new values are entered. How do get this code to work for values which I have already entered. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("C4:IR30")) Is Nothing Then Select Case Target Case "C", "c" icolor = 8 'light blue Case "D", "D" icolor = 9 'brown Case "G", "g" icolor = 6 'yellow Case "H", "h" icolor = 3 'red Case "K", "k" icolor = 7 'pink Case "L", "l" icolor = 4 'green Case "O", "o" icolor = 20 'light blue Case "S", "s" icolor = 10 'dark green Case "C", "c" icolor = 8 'light blue Case "X", "x" icolor = 15 'grey Case Else 'Whatever '1 black, 2 white, 5 dark blue, 11 dark blue End Select Target.Interior.ColorIndex = icolor End If End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 conditional formats in excel
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("C4:IR30") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X") nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15) For Each rr In r icolor = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then icolor = nums(i) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub -- Gary's Student "Manj" wrote: I have used the below code to get around the 3 conditional formats in excel. But this only work when new values are entered. How do get this code to work for values which I have already entered. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("C4:IR30")) Is Nothing Then Select Case Target Case "C", "c" icolor = 8 'light blue Case "D", "D" icolor = 9 'brown Case "G", "g" icolor = 6 'yellow Case "H", "h" icolor = 3 'red Case "K", "k" icolor = 7 'pink Case "L", "l" icolor = 4 'green Case "O", "o" icolor = 20 'light blue Case "S", "s" icolor = 10 'dark green Case "C", "c" icolor = 8 'light blue Case "X", "x" icolor = 15 'grey Case Else 'Whatever '1 black, 2 white, 5 dark blue, 11 dark blue End Select Target.Interior.ColorIndex = icolor End If End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 conditional formats in excel
Thanks Gary - This was excellent
"Gary''s Student" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("C4:IR30") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("C", "D", "G", "H", "K", "L", "O", "S", "C", "X") nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15) For Each rr In r icolor = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then icolor = nums(i) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub -- Gary's Student "Manj" wrote: I have used the below code to get around the 3 conditional formats in excel. But this only work when new values are entered. How do get this code to work for values which I have already entered. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("C4:IR30")) Is Nothing Then Select Case Target Case "C", "c" icolor = 8 'light blue Case "D", "D" icolor = 9 'brown Case "G", "g" icolor = 6 'yellow Case "H", "h" icolor = 3 'red Case "K", "k" icolor = 7 'pink Case "L", "l" icolor = 4 'green Case "O", "o" icolor = 20 'light blue Case "S", "s" icolor = 10 'dark green Case "C", "c" icolor = 8 'light blue Case "X", "x" icolor = 15 'grey Case Else 'Whatever '1 black, 2 white, 5 dark blue, 11 dark blue End Select Target.Interior.ColorIndex = icolor End If End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
More than 3 conditional formats in excel
Thanks Mike, Gary VBA code did the trick
"Mike" wrote: This should help http://www.microsoft.com/office/comm...7-ee3abfdb66d9 "Manj" wrote: I have used the below code to get around the 3 conditional formats in excel. But this only work when new values are entered. How do get this code to work for values which I have already entered. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("C4:IR30")) Is Nothing Then Select Case Target Case "C", "c" icolor = 8 'light blue Case "D", "D" icolor = 9 'brown Case "G", "g" icolor = 6 'yellow Case "H", "h" icolor = 3 'red Case "K", "k" icolor = 7 'pink Case "L", "l" icolor = 4 'green Case "O", "o" icolor = 20 'light blue Case "S", "s" icolor = 10 'dark green Case "C", "c" icolor = 8 'light blue Case "X", "x" icolor = 15 'grey Case Else 'Whatever '1 black, 2 white, 5 dark blue, 11 dark blue End Select Target.Interior.ColorIndex = icolor End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel: Having more than 3 conditional formats would be helpful. | Excel Worksheet Functions | |||
How do I keep graph "data table" formats in separate excel workboo | Charts and Charting in Excel | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Conditional Formats in Excel | Excel Worksheet Functions |