| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting limit to 3 Excel 2003
Hi,
Is there a way to increase the number of conditions? Here are my conditions: 1) Formula is =$R7="WP" Format: Bold white font + red Pattern 2) Formula is =$R7="PIP" Format: Bold white font + blue Pattern 3) Formula is =$R7="BD" Format: Bold white font + black Pattern This applies to any row from column A to column R I need to add at least 3-5 more conditions. Is this done using VBA?, can someone provide the code for it? Thank you, Andrew |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional formatting limit to 3 Excel 2003
ANDNAND,
I did a little research, seems that someone has created an add-in for Excel to increase this to 30. The add-in is called CFPlus. http://www.xldynamic.com/source/xld.....Download.html -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''''Yes'''' below. "ANDNAND" wrote: Hi, Is there a way to increase the number of conditions? Here are my conditions: 1) Formula is =$R7="WP" Format: Bold white font + red Pattern 2) Formula is =$R7="PIP" Format: Bold white font + blue Pattern 3) Formula is =$R7="BD" Format: Bold white font + black Pattern This applies to any row from column A to column R I need to add at least 3-5 more conditions. Is this done using VBA?, can someone provide the code for it? Thank you, Andrew |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional formatting limit to 3 Excel 2003
Thank you. I would rather like to use VBA than adding an add in. Is this
duable? "Thomas [PBD]" wrote: ANDNAND, I did a little research, seems that someone has created an add-in for Excel to increase this to 30. The add-in is called CFPlus. http://www.xldynamic.com/source/xld.....Download.html -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''''Yes'''' below. "ANDNAND" wrote: Hi, Is there a way to increase the number of conditions? Here are my conditions: 1) Formula is =$R7="WP" Format: Bold white font + red Pattern 2) Formula is =$R7="PIP" Format: Bold white font + blue Pattern 3) Formula is =$R7="BD" Format: Bold white font + black Pattern This applies to any row from column A to column R I need to add at least 3-5 more conditions. Is this done using VBA?, can someone provide the code for it? Thank you, Andrew |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional formatting limit to 3 Excel 2003
ANDNAND,
Chip Pearson does a pretty good run-down of the technique at: http://www.cpearson.com/excel/cformatting.htm Here is an example of how to work it: 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 -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''''Yes'''' below. "ANDNAND" wrote: Thank you. I would rather like to use VBA than adding an add in. Is this duable? "Thomas [PBD]" wrote: ANDNAND, I did a little research, seems that someone has created an add-in for Excel to increase this to 30. The add-in is called CFPlus. http://www.xldynamic.com/source/xld.....Download.html -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''''Yes'''' below. "ANDNAND" wrote: Hi, Is there a way to increase the number of conditions? Here are my conditions: 1) Formula is =$R7="WP" Format: Bold white font + red Pattern 2) Formula is =$R7="PIP" Format: Bold white font + blue Pattern 3) Formula is =$R7="BD" Format: Bold white font + black Pattern This applies to any row from column A to column R I need to add at least 3-5 more conditions. Is this done using VBA?, can someone provide the code for it? Thank you, Andrew |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional formatting limit to 3 Excel 2003
Thank you for helping. The code ample you provided works only for the
specific cell. How do I make it work so every cell on the row, from column A until column R get the specified format. (R contains the string to evaluate as I initially pointed) Thank you again, I hope you can help me out "Thomas [PBD]" wrote: ANDNAND, Chip Pearson does a pretty good run-down of the technique at: http://www.cpearson.com/excel/cformatting.htm Here is an example of how to work it: 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 -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''''Yes'''' below. "ANDNAND" wrote: Thank you. I would rather like to use VBA than adding an add in. Is this duable? "Thomas [PBD]" wrote: ANDNAND, I did a little research, seems that someone has created an add-in for Excel to increase this to 30. The add-in is called CFPlus. http://www.xldynamic.com/source/xld.....Download.html -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''''Yes'''' below. "ANDNAND" wrote: Hi, Is there a way to increase the number of conditions? Here are my conditions: 1) Formula is =$R7="WP" Format: Bold white font + red Pattern 2) Formula is =$R7="PIP" Format: Bold white font + blue Pattern 3) Formula is =$R7="BD" Format: Bold white font + black Pattern This applies to any row from column A to column R I need to add at least 3-5 more conditions. Is this done using VBA?, can someone provide the code for it? Thank you, Andrew |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Try this
Use something more like this:
Private Sub worksheet_change(ByVal Target As Range) Set MyPlage = Range("B476:IM476") For Each Cell In MyPlage If Cell.Value = "F Acid" Then Cell.Interior.ColorIndex = 43 Cell.Font.ColorIndex = 1 End If If Cell.Value = "S Acid" Then Cell.Interior.ColorIndex = 50 Cell.Font.ColorIndex = 1 End If If Cell.Value = "Stone" Then Cell.Interior.ColorIndex = 12 Cell.Font.ColorIndex = 1 End If If Cell.Value = "Epoxy" Then Cell.Interior.ColorIndex = 1 Cell.Font.ColorIndex = 2 End If If Cell.Value = "SSurf" Then Cell.Interior.ColorIndex = 38 Cell.Font.ColorIndex = 1 End If If Cell.Value = "SSteel" Then Cell.Interior.ColorIndex = 48 Cell.Font.ColorIndex = 1 End If If Cell.Value = "Misc" Then Cell.Interior.ColorIndex = 8 Cell.Font.ColorIndex = 1 End If If Cell.Value = "Flat" Then Cell.Interior.ColorIndex = 2 Cell.Font.ColorIndex = 1 End If If Cell.Value = "3/4Flat" Then Cell.Interior.ColorIndex = 2 Cell.Font.ColorIndex = 1 End If If Cell.Value = "Spl" Then Cell.Interior.ColorIndex = 2 Cell.Font.ColorIndex = 1 End If Next End Sub On Tuesday, October 07, 2008 1:43 PM ANDNAN wrote: Hi, Is there a way to increase the number of conditions? Here are my conditions: 1) Formula is =$R7="WP" Format: Bold white font + red Pattern 2) Formula is =$R7="PIP" Format: Bold white font + blue Pattern 3) Formula is =$R7="BD" Format: Bold white font + black Pattern This applies to any row from column A to column R I need to add at least 3-5 more conditions. Is this done using VBA?, can someone provide the code for it? Thank you, Andrew On Tuesday, October 07, 2008 2:19 PM ThomasPB wrote: ANDNAND, I did a little research, seems that someone has created an add-in for Excel to increase this to 30. The add-in is called CFPlus. http://www.xldynamic.com/source/xld.....Download.html -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''''Yes'''' below. "ANDNAND" wrote: On Tuesday, October 07, 2008 2:28 PM ANDNAN wrote: Thank you. I would rather like to use VBA than adding an add in. Is this duable? "Thomas [PBD]" wrote: On Tuesday, October 07, 2008 3:05 PM ThomasPB wrote: ANDNAND, Chip Pearson does a pretty good run-down of the technique at: http://www.cpearson.com/excel/cformatting.htm Here is an example of how to work it: 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 -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''''Yes'''' below. "ANDNAND" wrote: On Tuesday, October 07, 2008 6:23 PM ANDNAN wrote: Thank you for helping. The code ample you provided works only for the specific cell. How do I make it work so every cell on the row, from column A until column R get the specified format. (R contains the string to evaluate as I initially pointed) Thank you again, I hope you can help me out "Thomas [PBD]" wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice Make The WebClient Class follow redirects and get Target Url http://www.eggheadcafe.com/tutorials...arget-url.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting size limit | Excel Discussion (Misc queries) | |||
| Conditional Formatting - Excel 2003 | Excel Discussion (Misc queries) | |||
| Conditional Formatting - Getting pass the 3 condition limit | Excel Discussion (Misc queries) | |||
| Limit to Conditional Formatting | Excel Discussion (Misc queries) | |||
| Conditional Formatting - Getting pass the 3 condition limit | Excel Discussion (Misc queries) | |||
Linear Mode
