contitional formating with more then three options
 

How do i do contidional formating in a cell with more then three options?
What does the code look like for a vba script? I know it can be applied to
the entire worksheet. For example if a cell contains the word ALL i want that
cell to be orange. If a cell contains the work Supervisor I want it to be
grey...etc.

thanks

contitional formating with more then three options
 

Have a look here
http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"peterg" wrote in message
...
How do i do contidional formating in a cell with more then three options?
What does the code look like for a vba script? I know it can be applied to
the entire worksheet. For example if a cell contains the word ALL i want
that
cell to be orange. If a cell contains the work Supervisor I want it to be
grey...etc.

thanks

contitional formating with more then three options
 

Probably something like this, which i found on an old post and modified just
slightly for your requirements:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As String
If Target.Cells.Count 1 Then Exit Sub
If Target = "" Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:Z1000") 'change to suit

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "All"
Target.Interior.ColorIndex = 46
Case "Manager"
Target.Interior.ColorIndex = 16
Case "Supervisor"
Target.Interior.ColorIndex = 3
Case "Employee"
Target.Interior.ColorIndex = 4

End Select
End If
End Sub

Notice, to make this work, right-click the sheet that you want to use this
in, click View Code, and paste the code into the window that opens.
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"peterg" wrote:

How do i do contidional formating in a cell with more then three options?
What does the code look like for a vba script? I know it can be applied to
the entire worksheet. For example if a cell contains the word ALL i want that
cell to be orange. If a cell contains the work Supervisor I want it to be
grey...etc.

thanks

contitional formating with more then three options
 

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10") 'adjust to suit range
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("Cat", "Dog", "Goat", "Horse", "Lion", "Ocelot") 'edit to suit
nums = Array(8, 9, 6, 3, 7, 4) 'edit color indexes to suit
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.

Make the appropriate edits to range and vals and nums


Gord Dibben MS Excel MVP

On Thu, 7 Jan 2010 08:02:03 -0800, peterg
wrote:

How do i do contidional formating in a cell with more then three options?
What does the code look like for a vba script? I know it can be applied to
the entire worksheet. For example if a cell contains the word ALL i want that
cell to be orange. If a cell contains the work Supervisor I want it to be
grey...etc.

thanks

contitional formating with more then three options
 

Or you can use Excel '07 if you don't want to get into macros.

On Jan 7, 11:11*am, "Bernard Liengme"
wrote:
Have a look herehttp://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm
best wishes
--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme

"peterg" wrote in message

...

How do i do contidional formating in a cell with more then three options?
What does the code look like for a vba script? I know it can be applied to
the entire worksheet. For example if a cell contains the word ALL i want
that
cell to be orange. If a cell contains the work Supervisor I want it to be
grey...etc.

thanks

contitional formating with more then three options
 

Hey, I've got a quick question. How can this be set up to handle things like
'manager' instead of 'Manager'? I was thinking about this too: something
like 'my manager is really annoying!!'

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Gord Dibben" wrote:

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10") 'adjust to suit range
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("Cat", "Dog", "Goat", "Horse", "Lion", "Ocelot") 'edit to suit
nums = Array(8, 9, 6, 3, 7, 4) 'edit color indexes to suit
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.

Make the appropriate edits to range and vals and nums


Gord Dibben MS Excel MVP

On Thu, 7 Jan 2010 08:02:03 -0800, peterg
wrote:

How do i do contidional formating in a cell with more then three options?
What does the code look like for a vba script? I know it can be applied to
the entire worksheet. For example if a cell contains the word ALL i want that
cell to be orange. If a cell contains the work Supervisor I want it to be
grey...etc.

thanks

.

contitional formating with more then three options
 

@Ryan

If you're going the macro route, you can use commas to separate
options:
Case "Manager", "manager", "mgr"

If you're using conditional formatting, you can use something like:
lower(h10) = "manager"
plus you always have the OR function too.

On Jan 7, 3:16*pm, ryguy7272
wrote:
Hey, I've got a quick question. *How can this be set up to handle things like
'manager' instead of 'Manager'? *I was thinking about this too: something
like 'my manager is really annoying!!'

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..

"Gord Dibben" wrote:
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A10") * 'adjust to suit range
If Intersect(Target, r) Is Nothing Then
* * Exit Sub
End If
vals = Array("Cat", "Dog", "Goat", "Horse", "Lion", "Ocelot") *'edit to suit
nums = Array(8, 9, 6, 3, 7, 4) *'edit color indexes to suit
For Each rr In r
* * icolor = 0
* * For i = LBound(vals) To UBound(vals)
* * * * If rr.Value = vals(i) Then
* * * * * * icolor = nums(i)
* * * * End If
* * Next
* * If icolor 0 Then
* * rr.Interior.ColorIndex = icolor
* * End If
Next
End Sub

Right-click on the sheet tab and "View Code". *Copy/paste the code into that
sheet module.

Make the appropriate edits to range and vals and nums

Gord Dibben *MS Excel MVP

On Thu, 7 Jan 2010 08:02:03 -0800, peterg
wrote:

How do i do contidional formating in a cell with more then three options?
What does the code look like for a vba script? I know it can be applied to
the entire worksheet. For example if a cell contains the word ALL i want that
cell to be orange. If a cell contains the work Supervisor I want it to be
grey...etc.

thanks

.

contitional formating with more then three options
 

Option Compare Text which I placed at top of event code takes care of case
sensitivity.


Gord

On Thu, 7 Jan 2010 12:16:06 -0800, ryguy7272
wrote:

Hey, I've got a quick question. How can this be set up to handle things like
'manager' instead of 'Manager'? I was thinking about this too: something
like 'my manager is really annoying!!'

contitional formating with more then three options
 

But not within larger strings as per your example.

You want to color just the text "manager" or color the entire cell if any
form of manager is found?


Gord

On Thu, 07 Jan 2010 15:11:14 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Option Compare Text which I placed at top of event code takes care of case
sensitivity.


Gord

On Thu, 7 Jan 2010 12:16:06 -0800, ryguy7272
wrote:

Hey, I've got a quick question. How can this be set up to handle things like
'manager' instead of 'Manager'? I was thinking about this too: something
like 'my manager is really annoying!!'