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
Similar Threads
