Conditional formatting limit to 3 Excel 2003

  • Thread starter Thread starter ANDNAND
  • Start date Start date
A

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
 
Thank you. I would rather like to use VBA than adding an add in. Is this
duable?

Thomas said:
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 said:
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,

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 said:
Thank you. I would rather like to use VBA than adding an add in. Is this
duable?

Thomas said:
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 said:
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
 
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 said:
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 said:
Thank you. I would rather like to use VBA than adding an add in. Is this
duable?

Thomas said:
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
 
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:
 
Back
Top