Conditional formatting limit to 3 Excel 2003

[ANDNAND]

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

 

[Thomas [PBD]]

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.CFPlus.Download.html

 

[ANDNAND]

Thank you. I would rather like to use VBA than adding an add in. Is this
duable?

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.CFPlus.Download.html

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

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

 

[Thomas [PBD]]

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.

Thank you. I would rather like to use VBA than adding an add in. Is this
duable?

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.CFPlus.Download.html

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

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

 

[ANDNAND]

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

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.

Thank you. I would rather like to use VBA than adding an add in. Is this
duable?

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.CFPlus.Download.html

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.


:

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

 

[Jarred Church]

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

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.CFPlus.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: