contitional formating with more then three options

[peterg]

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

[ryguy7272]

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''.

[Gord Dibben]

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 <pet...@discussions.microsoft.com>
wrote:

[Bernard Liengme]

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

"peterg" <pet...@discussions.microsoft.com> wrote in message
news:4C81BC09-9EC9-471E...@microsoft.com...

[Reitano Family]

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

On Jan 7, 11:11 am, "Bernard Liengme" <blien...@TRUENORTH.stfx.ca>
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

[ryguy7272]

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:

> .
>

[Reitano Family]

@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 <ryguy7...@discussions.microsoft.com>
wrote:

[Gord Dibben]

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

Gord

[Gord Dibben]

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