conditional formatting

  • Thread starter Thread starter jason
  • Start date Start date
J

jason

Is it possible, through an add-in or otherwise, to have
more than 3 conditions formatted? In my spreadsheet, I
have lots of addresses, names, preferences, etc. Let's
say I want to highlight every entry from Illinois, every
person whose last name is Smith, everybody whose favorite
color is blue, and every person who career is categorized
as administrative. As you can see, I have four categories
here. In actuality, I have many more, but this works for
my question. As suggested, I want to highlight these
entries and want every new entry that meets one of these
conditions to be automatically highlighted.
 
I don't think there's a way to do more than 3, but if you use only 3
different highlight colors, you can apply multiple conditions to each
color. In each condition, use a formula: ie.
=OR($A75="Jan.",$A75="Apr.",$A75="Jul.",$A75="Oct.").

It's a workaround, I know, but I don't know another way...
 
Using a vba macro you can set as many conditional formats as you like. The
code that follows has five in it. Depending on whether the value in the
cell A1 is a,b,c,d or e the cell will be made bold and a different color.
The #'s that represent the colors can be found in the visual basic editor,
select help, type in colorindex, then click colorindex property. If the
conditions are more complex then something other than the if, then, else
statements may be more appropriate but you get the idea.

Steve



Sub Macro1()
Range("a1").Select
If ActiveCell = "a" Then
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""a"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 18
End With
Else
If ActiveCell = "b" Then
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""b"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 5
End With
Else
If ActiveCell = "c" Then
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""c"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 26
End With
Else
If ActiveCell = "d" Then
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""d"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 15
End With
Else
If ActiveCell = "e" Then
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""e"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 12
End With
End If
End If
End If
End If
End If
End Sub
 
I have a page on Conditional Formatting that might help.
http://www.mvps.org/dmcritchie/excel/condfmt.htm

If a cell needs more than 3 colors choices plus default the
obvious direction to go is for an Event Macro
http://www.mvps.org/dmcritchie/excel/event.htm#case

Another remote possibility is filtering. I say remote because
you'd want all conditions tested to be true to still see the row.
You can read about Filter and Advanced Filter at Debra Dalgleish's
http://www.contextures.com

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Back
Top