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