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

 

[Bernard Liengme]

Have a look here
http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm
best wishes

 

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

 

[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

 

[Reitano Family]

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

 

[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!!'

 

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

 

[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