| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than 4 conditional formating?
How do I get mon the 4 conditions on Range("A4:O500") I would like to increase it to 7 Here is my current code Cells.Select Selection.FormatConditions.Delete Selection.Interior.ColorIndex = xlNone Cells.Select Selection.Interior.ColorIndex = xlNone Range("A4:O500").Select With Selection.Interior .ColorIndex = xlNone .Pattern = xlSolid End With Range("A4:M150").Select 'Using these three condition ' Always use the following line first for Cond Frmtng Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$N4=""SHIPPED""" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$I4=""IN PROUTE""" Selection.FormatConditions(2).Interior.ColorIndex = 38 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$I4=""AMEX""" Selection.FormatConditions(3).Interior.ColorIndex = 6 Range("N4:O150").Select 'Using these three condition ' Always use the following line first for Cond Frmtng Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$N4=""NOT SHIPPED""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$N4=""SHIPPED""" Selection.FormatConditions(2).Interior.ColorIndex = 4 Range("A1").Select |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than 4 conditional formating?
Hi Little Penny,
Conditional formatting, as such, only supports 4 conditions - the default state plus 3 others. If you need more than this, you'll need to use vba to format the range directly. Cheers -- macropod [MVP - Microsoft Word] "Little Penny" wrote in message ... How do I get mon the 4 conditions on Range("A4:O500") I would like to increase it to 7 Here is my current code Cells.Select Selection.FormatConditions.Delete Selection.Interior.ColorIndex = xlNone Cells.Select Selection.Interior.ColorIndex = xlNone Range("A4:O500").Select With Selection.Interior .ColorIndex = xlNone .Pattern = xlSolid End With Range("A4:M150").Select 'Using these three condition ' Always use the following line first for Cond Frmtng Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$N4=""SHIPPED""" Selection.FormatConditions(1).Interior.ColorIndex = 4 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$I4=""IN PROUTE""" Selection.FormatConditions(2).Interior.ColorIndex = 38 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$I4=""AMEX""" Selection.FormatConditions(3).Interior.ColorIndex = 6 Range("N4:O150").Select 'Using these three condition ' Always use the following line first for Cond Frmtng Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$N4=""NOT SHIPPED""" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=$N4=""SHIPPED""" Selection.FormatConditions(2).Interior.ColorIndex = 4 Range("A1").Select |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than 4 conditional formating?
How do I do this.
I found this code on VBAexpress.com How do I convert my exsiting code into a case select code Option Compare Text 'A=a, B=b, ... Z=z Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range Dim Rng1 As Range On Error Resume Next Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error Goto 0 If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address) Else Set Rng1 = Union(Range(Target.Address), Rng1) End If For Each Cell In Rng1 Select Case Cell.Value Case vbNullString Cell.Interior.ColorIndex = xlNone Cell.Font.Bold = False Case "Tom", "Joe", "Paul" Cell.Interior.ColorIndex = 3 Cell.Font.Bold = True Case "Smith", "Jones" Cell.Interior.ColorIndex = 4 Cell.Font.Bold = True Case 1, 3, 7, 9 Cell.Interior.ColorIndex = 5 Cell.Font.Bold = True Case 10 To 25 Cell.Interior.ColorIndex = 6 Cell.Font.Bold = True Case 26 To 99 Cell.Interior.ColorIndex = 7 Cell.Font.Bold = True Case Else Cell.Interior.ColorIndex = xlNone Cell.Font.Bold = False End Select Next End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than 4 conditional formating?
Hi Little Penny,
How do I do this. Well, you'd have to change that code to suit your needs. For example, change: Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error Goto 0 If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address) Else Set Rng1 = Union(Range(Target.Address), Rng1) End If For Each Cell In Rng1 to something like: If .Row = 5 And .Row <= 10 And .Column = 3 And .Column <= 10 Then to define the range you want to apply the formatting to (the above defines the range as C5:J10), and modify the Select case procedure, to something like: Select Case Cell.Value Case vbNullString Cell.Interior.ColorIndex = xlNone Case "NOT SHIPPED" Cell.Interior.ColorIndex = 3 Case "SHIPPED" Cell.Interior.ColorIndex = 4 Case "IN PROUTE" Cell.Interior.ColorIndex = 38 Case "AMEX" Cell.Interior.ColorIndex = 6 ' Add more coses as needed, then finish off with the next two lines to ' deal with anything not already catered for. Case Else Cell.Interior.ColorIndex = xlNone End Select and change the 'Next' to 'End If'. -- macropod [MVP - Microsoft Word] "Little Penny" wrote in message ... How do I do this. I found this code on VBAexpress.com How do I convert my exsiting code into a case select code Option Compare Text 'A=a, B=b, ... Z=z Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range Dim Rng1 As Range On Error Resume Next Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error Goto 0 If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address) Else Set Rng1 = Union(Range(Target.Address), Rng1) End If For Each Cell In Rng1 Select Case Cell.Value Case vbNullString Cell.Interior.ColorIndex = xlNone Cell.Font.Bold = False Case "Tom", "Joe", "Paul" Cell.Interior.ColorIndex = 3 Cell.Font.Bold = True Case "Smith", "Jones" Cell.Interior.ColorIndex = 4 Cell.Font.Bold = True Case 1, 3, 7, 9 Cell.Interior.ColorIndex = 5 Cell.Font.Bold = True Case 10 To 25 Cell.Interior.ColorIndex = 6 Cell.Font.Bold = True Case 26 To 99 Cell.Interior.ColorIndex = 7 Cell.Font.Bold = True Case Else Cell.Interior.ColorIndex = xlNone Cell.Font.Bold = False End Select Next End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than 4 conditional formating?
Thanks for your replies
Here is where my lack of experience shows I don't know how to format the if statement to select the range A4:M500 is my range Based on the value on cell (N) I want the case select to color the row A thru M a certain color. On Sun, 19 Nov 2006 13:44:08 +1100, "macropod" wrote: Hi Little Penny, How do I do this. Well, you'd have to change that code to suit your needs. For example, change: Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error Goto 0 If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address) Else Set Rng1 = Union(Range(Target.Address), Rng1) End If For Each Cell In Rng1 to something like: If .Row = 5 And .Row <= 10 And .Column = 3 And .Column <= 10 Then to define the range you want to apply the formatting to (the above defines the range as C5:J10), and modify the Select case procedure, to something like: Select Case Cell.Value Case vbNullString Cell.Interior.ColorIndex = xlNone Case "NOT SHIPPED" Cell.Interior.ColorIndex = 3 Case "SHIPPED" Cell.Interior.ColorIndex = 4 Case "IN PROUTE" Cell.Interior.ColorIndex = 38 Case "AMEX" Cell.Interior.ColorIndex = 6 ' Add more coses as needed, then finish off with the next two lines to ' deal with anything not already catered for. Case Else Cell.Interior.ColorIndex = xlNone End Select and change the 'Next' to 'End If'. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than 4 conditional formating?
how about select case instead of using if.. then
http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm -- Gary "Little Penny" wrote in message ... Thanks for your replies Here is where my lack of experience shows I don't know how to format the if statement to select the range A4:M500 is my range Based on the value on cell (N) I want the case select to color the row A thru M a certain color. On Sun, 19 Nov 2006 13:44:08 +1100, "macropod" wrote: Hi Little Penny, How do I do this. Well, you'd have to change that code to suit your needs. For example, change: Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error Goto 0 If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address) Else Set Rng1 = Union(Range(Target.Address), Rng1) End If For Each Cell In Rng1 to something like: If .Row = 5 And .Row <= 10 And .Column = 3 And .Column <= 10 Then to define the range you want to apply the formatting to (the above defines the range as C5:J10), and modify the Select case procedure, to something like: Select Case Cell.Value Case vbNullString Cell.Interior.ColorIndex = xlNone Case "NOT SHIPPED" Cell.Interior.ColorIndex = 3 Case "SHIPPED" Cell.Interior.ColorIndex = 4 Case "IN PROUTE" Cell.Interior.ColorIndex = 38 Case "AMEX" Cell.Interior.ColorIndex = 6 ' Add more coses as needed, then finish off with the next two lines to ' deal with anything not already catered for. Case Else Cell.Interior.ColorIndex = xlNone End Select and change the 'Next' to 'End If'. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More than 4 conditional formating?
Hi Little Penny,
Try this: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Line As Range On Error Resume Next With Target If .Row = 4 And .Row <= 500 And .Column = 1 And .Column <= 13 Then Set Line = Range(Cells(.Row, 1), Cells(.Row, 13)) Select Case .Value Case vbNullString Line.Interior.ColorIndex = xlNone Case "NOT SHIPPED" Line.Interior.ColorIndex = 3 Case "SHIPPED" Line.Interior.ColorIndex = 4 Case "IN PROUTE" Line.Interior.ColorIndex = 38 Case "AMEX" Line.Interior.ColorIndex = 6 ' Add more cases as needed, then finish off with the next two lines to ' deal with anything not already catered for. Case Else Line.Interior.ColorIndex = xlNone End Select End If End With End Sub I'm not clear as to whether you're testing for the presence of the strings anywhere in A4:M500, or just in a particular column. I've coded for the former. If it's the latter, change the line: If .Row = 4 And .Row <= 500 And .Column = 1 And .Column <= 13 Then to suit. Either way, whatever row is affected will have columns A:M shaded. This range is controlled by the line: Set Line = Range(Cells(.Row, 1), Cells(.Row, 13)) Cheers -- macropod [MVP - Microsoft Word] "Little Penny" wrote in message ... Thanks for your replies Here is where my lack of experience shows I don't know how to format the if statement to select the range A4:M500 is my range Based on the value on cell (N) I want the case select to color the row A thru M a certain color. On Sun, 19 Nov 2006 13:44:08 +1100, "macropod" wrote: Hi Little Penny, How do I do this. Well, you'd have to change that code to suit your needs. For example, change: Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error Goto 0 If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address) Else Set Rng1 = Union(Range(Target.Address), Rng1) End If For Each Cell In Rng1 to something like: If .Row = 5 And .Row <= 10 And .Column = 3 And .Column <= 10 Then to define the range you want to apply the formatting to (the above defines the range as C5:J10), and modify the Select case procedure, to something like: Select Case Cell.Value Case vbNullString Cell.Interior.ColorIndex = xlNone Case "NOT SHIPPED" Cell.Interior.ColorIndex = 3 Case "SHIPPED" Cell.Interior.ColorIndex = 4 Case "IN PROUTE" Cell.Interior.ColorIndex = 38 Case "AMEX" Cell.Interior.ColorIndex = 6 ' Add more coses as needed, then finish off with the next two lines to ' deal with anything not already catered for. Case Else Cell.Interior.ColorIndex = xlNone End Select and change the 'Next' to 'End If'. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional formating | New Users to Excel | |||
| Conditional formating | Excel Discussion (Misc queries) | |||
| Conditional Formating | Excel Worksheet Functions | |||
| Conditional Formating | Excel Worksheet Functions | |||
| Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
Hybrid Mode
