contitional formating with more then three options

  • Thread starter Thread starter peterg
  • Start date Start date
P

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
 
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.
 
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
 
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 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.
 
Option Compare Text which I placed at top of event code takes care of case
sensitivity.


Gord
 
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
 
Back
Top