Conditional Formatting was introduced with Excel 97 and is a terrific feature, but there is a limit of 3 conditional sets per cell (like 3 wishes). Conditional Formatting, while in effect for a cell, will override the text colors that can be produced for numeric values by normal cell formatting.
Conditional Formatting, while in effect for a cell, will override normal interior, text, and number formatting colors. A terrific feature with some severe limitations (limit of 3 conditions per group, hard to find when and where C.F. conditions are used). Nevertheless, if you remove all of the C.F. it will be gone, no additional changes are needed to get back to where you were before adding C.F.
Filters, Validation, and Conditional Formatting are very similar to one another. The purpose of the formula is test a condition and return either True or False, and act upon it if the condition is True.
Adding Conditions
There is a limit of three. The first of the 1 to 3 conditions that matches will be used. 
If you need more than 3 colors for a cell (plus the default) then you can not use C.F. alone and you would probably want to use an Event macro and would have to specify your colors colors rather than simply selecting from a toolbar table. Another alternative is to use Extended Conditional Formatter Excel addin (up to 50) that you use exactly like C.F., or to use Excel 2007 which removes the color limit per cell but still has a limit of 56 colors per workbook. In reality with more than 5 colors you will likely create presentation problems instead of reducing ambiguity.
What you can change with Conditional Formatting
You can include in each C.F any or all of the things you can change with a C.F.
font: style, strike through, and/or color
border: which borders, limited number of styles, and/or color)
patterns (interior): pattern and/or color
The idea behind Conditional Formatting and
filters for that matter is to test for a condition,
which
will provide either TRUE (not zero) or FALSE (zero).
=1=1 will produce TRUE because 1=1
=1=2 will produce FALSE because 1<>2 (not equal to)
Which cells can be get coloring is based on the selection.
The formula itself is based on the active cell.
Each cell in the selection will be tested and uses 1 of the 3 conditions per cell that you are allowed in C.F.
Therefore, you would make the column portions absolute in your C.F. formula, when you want to color by rows. 
The formula you create for Conditional Formatting is based
on the cell that is currently active. The cells affected (to be colored) are
those in the Selection Range. « This distinction is very
important to being able to understand and use Conditional Formatting.
[more explicit]
Conditional Formatting
is based on the active cell. The selection indicates which cells can be changed.
If you select the entire worksheet, the
active cell is generally A1, but it depends on what is at the top of your view when
you make the selection. If you select a column, the active cell is at the top
of the column portion in view.
If you want to color more than one column (i.e. the entire row) you would make the column portion of the address absolute, $B instead of B, so that the comparison applies only to Column B, and the coloring would be to whatever columns you selected when entering your Conditional Format(s).;
At this point, if you have not used Conditional Formatting before, you might take a break here and look at a Video tutorial see “About Conditional Formatting” (datapigtechnologies) and then return to this page.
Whatever is not covered with Conditional Formatting will be covered by normal cell formatting so if you want the default to be black italic you can format the column as such with normal cell formatting. (Examples of number format in normal cell formatting)
The format painter will copy both normal cell formatting and Conditional Formatting to additional cells. The format painter is an optional button on the toolbar that looks like a paint brush.
You can identify which cells have Conditional Formatting by using Edit, Goto, Conditional Formatting. You can see what the Conditional Formatting is for a specific cell or a group by selecting the cell(s) and Format, Conditional Formatting, which will show the already existing Conditional Formatting.
References:
You may not use references to other worksheets or Workbooks for Conditional Formatting criteria.
This is not much of an obstacle, simple use a named range to refer to a range on another worksheet.
The restriction also means that you cannot use a formula referencing your
personal.xls in a user defined function. You can get around that by
creating a reference in your VBE from your workbook to your personal.xls
Errors and Empty Cells
Don't worry about the empty cells, if you didn't include them in your
test you won't be adding color. Conditional formatting has to get
a True condition or a False condition – that is the whole purpose of C.F.
Errors such as text in a numeric test are treated as False so you don't need to include a lot
of additional tests as when you are trying to calculate a value.
If you wanted to show empty cells as an incomplete condition
C.F. formula 3 is: =TRIM(D3}=""
or
C.F. formula 3 is: =AND(TRIM(D3)="", A3<>"")
Just so nobody thinks of deleting their workbook because there were no directions to remove Conditional Formatting, this is the safest way to remove C.F. from the activesheet.
To Eliminate all Conditional Formatting in a Worksheet
Ctrl+A (foobared in Excel 2003 hit Ctrl+A twice)
Format, Condition Formatting, delete button, check all 3 format boxes, OKFrom VBA use: selection.formatconditions.delete
Select Column B if just coloring Column B, or select any combination of columns, or all columns (Ctrl+A (foobared in Excel 2003 hit Ctrl+A twice)) Format > Conditional Formatting [add format] for condition 2 [add format] for condition 3
condition 1 Formula is: (due today, current date)
condition 2 Formula is: (within last 8 days)
condition 3 Formula is: (within next 8 days)



The second will highlight numbers that are greater than one
year from today (yellow interior).
=AND(ISNUMBER($B1),$B1>=DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY())))
=TODAY()=$B1
You can test the formula on the worksheet first if you like (as in column C).
For example the formula in C5 which is copied down with fillhandle is:
=AND(ISNUMBER($B5),$B5>=DATE(YEAR(TODAY())+1,MONTH(TODAY()),DAY(TODAY())))


To highlight only in Column B, select column B before invoking C.F. To highlight the entire row, select ALL cells (same as all columns) before invoking C.F. and the $B will keep the checking within Column B.
(Additional Date examples below)
Select all cells (Ctrl+a) (foobared in Excel 2003 hit Ctrl+A twice)
menu: Format, Conditional Formatting, Formula is on drop down box
Condition 1: =$D1<=1.00 [interior Color 35 Light Green]
Condition 2: =$D1<=1.05 [interior Color 36 Light Yellow]
Condition 3: =$D1>1.05 [interior Color 38 Rose]
Since the conditions are checked in order it is frequently unnecessary
to have more than one comparison in a condition. For example in
Condition 2 above, we do not need to write the condition as
=AND($D1>1.00,=$D1<1.05)
You might for instance change the first condition to =AND($D1<=1.00,$D1>0) so that normal cell formatting will be seen for zero and negative numbers.
A  B  C  D  E  
1  description  Col(B)  Col(C)  =B/C  =GetFormula(Dn) 
2  description 1  33  200  0.165  =IF(ISERROR(B2/C2),"",B2/C2) 
3  description 2  500  475  1.053  =IF(ISERROR(B3/C3),"",B3/C3) 
4  description 3  105  100  1.050  =IF(ISERROR(B4/C4),"",B4/C4) 
5  description 4  5  4  (1.250)  =IF(ISERROR(B5/C5),"",B5/C5) 
6  description 5  560  400  1.400  =IF(ISERROR(B6/C6),"",B6/C6) 
7  description 6  75  160  0.469  =IF(ISERROR(B7/C7),"",B7/C7) 
8  description 7  0  1  0.000  =IF(ISERROR(B8/C8),"",B8/C8) 
9  description 8  =IF(ISERROR(B9/C9),"",B9/C9)  
10  description 9  1  0.000  =IF(ISERROR(B10/C10),"",B10/C10) 
In order to not color the vast unused rows and cells without
numbers the actual Conditional Formatting used in the above
example is as follows:
Condition 1: =AND(TRIM($D1)<>"",$D1<=1.00)
Condition 2: =AND(TRIM($D1)<>"",$D1<=1.05)
Condition 3: =AND(TRIM($D1)<>"",$D1>1.05,ISNUMBER($D1))
hint: When filling in the formulas press F2 so that you can use backspace to correct mistakes, also a good idea when working on cells in a spreadsheet.
While setting up each condition before completion of that condition, you change the text color, interior color pattern, borders as desired. Since interior color will wipe out gridlines, you might also want to include borders.
When a condition is removed, it is like it never existed. Conditional Formatting therefore will not leave garbage such as colored cells laying around once removed. You may have a problem finding your Conditional Formatting though.
Select all cells (Ctrl+a, foobared in Excel 2003 hit Ctrl+A twice)
menu: Format, Conditional Formatting, Formula is on drop down box
Condition 1: =COUNTIF(A1:I1,"completed")>3 [interior Color 35 Light Green]
Condition 2: value equal to: completed [interior Color 36 Light Yellow]
A  B  C  D  E  F  G  H  I  
1  Sample  5  Test 1  3  Test 2  10  Test 3  3  Test 4 
2  1014  5  Completed  2  2 of 3  10  Completed  2  2 of 3 
3  1015  5  Completed  3  Completed  10  Completed  3  Completed 
4  1016  4  4 of 5  of 3  of 10  of 3  
5  1017  5  Completed  3  Completed  9  9 of 10  3  Completed 
6  1018  5  Completed  3  Completed  10  Completed  3  Completed 
7  1019  1  1 of 5  of 3  of 10  of 3  
8  1020  of 5  of 3  of 10  of 3  
9  1021  5  Completed  3  Completed  10  Completed  2  2 of 3 
10  1022  of 5  of 3  of 10  of 3  
11  nnn  nnnnnnnnnnnnn  nnn  nnnnnnnnnnnnn  nnn  nnnnnnnnnnnnn  nnn  nnnnnnnnnnnnn  
12  C2:  =IF(B2>= B$1,"Completed",B2 & " of " & B$1) 
If you wanted to be more specific with the C.F. you could to something like:Select ALL cells for condition 1:
format > Conditional Formatting >
formula1 is: =SUM($c1="completed",$e1="completed",$g1="completed",$i1="completed")>=4Select columns: C, E, G, I for condition 2:
format > Conditional Formatting >
Formula2 is: ="completed"
Normally color banding uses a formula like
=MOD(ROW(),2)=0 for alternate row banding
=MOD(ROW(),3)=0 for every 3rd row colored
for more information on this form of color banding and adjusting
which lines will be banded see
Chip Pearson’s page on banding.
On the other hand if you have filtering in place and want to
band only the visible rows then the above will not work.
If you have something in Column A for every row you can use
SUBTOTAL(3,... in your Conditional Formula,
which is COUNTA
a count of the non empty cells in the list.
Use =MOD(SUBTOTAL(3,$A$1:$A1),2) for alternate row banding
Use =MOD(SUBTOTAL(3,$A$1:$A1),3) for every 3rd row colored


Additional information on Color Banding can be found in the Related Area at the bottom of this page.
 
 

The basic formula for the required helper column is
E1: 0
E2: =MOD(OFFSET($E2,1,0)+ OR($A2<>OFFSET($A2,1,0), $B2<>OFFSET($B2,1,0)),2)
a variation with a different color for a blank row
E2: =IF($A2="",IF($A1="",$E1,$E1+2),
MOD(OFFSET($E2,1,0)+ OR($A2<>OFFSET($A2,1,0), $B2<>OFFSET($B2,1,0)),2))
 CF1: =$E1=1 (yellow), CF2: =$E1>1 (blue for value 2 & 3)
Do Not use a blank row to separate within equal group.
The use of OFFSET is necessary so that the rows can be inserted/deleted/sorted without problems with formulas.
For Conditional formatting part. Select all cells, the only requirement for the active cell is that it be on Row 1. Select cell E1 then use Ctrl+A (foobared in Excel 2003 hit Ctrl+A twice)) to select all cells keeping the current cell active.
Conditional Formatting Formula 1 is: =$E1=1
A brief explanation concerning inadequate helper column formulas
not taking the two columns (lastname & firstname) into consideration:
The orange color banding example at the right shows Tom in the same band even though
one is Tom Taylor and the other is Tom Ulmer (two different people), fails becasue
lastname was not also checked.
For the Helper column it should be obvious that the first formula (below) would be inadequate as denoted in the second table with orange highlighting.
E2: =MOD($E1+ $B2<>$B1,2)
– inadequate because it only checks column B
E2: =MOD($E1+ OR($A2<>$A1, $B2<>$B1),2)
– almost there but doesn't allow insert/delete of rows
and that you need to check both Column A and Column B for changes,
and to facilitate rearrange of cells you need to include OFFSET.
E2: =MOD(OFFSET($E2,1,0)+ OR($A2<>OFFSET($A2,1,0), $B2<>OFFSET($B2,1,0)),2)
– this is the working formula shown at top of this section with the yellow color banding.
If you are only interested in grouping in Column A then your formulas in the helper column would be
E1: 0
E2: =MOD(OFFSET($E2,1,0)+ ($A2<>OFFSET($A2,1,0)),2)
and use the fill handle to fill down from E2. Use of OFFSET allows you to
insert/delete rows without having to fix formulas.

Checking for Duplicates  
alternate formula: Peo Sjoblom in the same thread. =IF(COUNTIF($B$1:B1,B1)>1,"Number exists in cell" &ADDRESS(MATCH(B1,$B$1:B1,0),COLUMN(B1),4),"") 
Some specific examples, as they appear in newsgroups:
These examples tell what to select before invoking Format > Conditional Formatting and all are Formula is, and after coding your formula, choose your format (pattern and font color), the first you see without selecting a tab is the FONT. When choosing an interior color you might also choose the borders because gridlines disappear with interior color and the borders chosen will only exist while C.F. is in effect.
To get smallest half (rounded up) of the entries in bold, change the formatting
formula to
Formula is: =A1<=SMALL($A1:$T1,(COUNTA(A$1:T$1)+1)/2)
To highlight dates greater than 10/14/2002
Cells with Formulas: (#withformulas)
You will need a User Defined Function for this and your UDF
must be in the same workbook or referenced in your VBE (tools, reference).
You cannot use =personal.xls!HasFormula(A1) for instance.
Formula is: =HasFormula(A1)
Function HasFormula(cell) HasFormula = cell.HasFormula 'in same workbook for C.Formatting End FunctionAlternative: You can temporarily see which cells have formulas with Ctrl+A, Edit, GoTo, [Special], Formulas, (check all: Numbers, Text, Logicals, Errors) then if you wish you can color the selected cells Format, cells, patter, or as an alternative see Get.Cell below. (Ctrl+A is foobared in Excel 2003 hit Ctrl+A twice)Since you must have the UDF in the same workbook the following with a bit more customization might be of interest for determining Manual overrides in a column that normally contains all formulas. Purposely omits row 1 and cells that are empty from returning True: [20041104]
Function cf_NotFormula(cell) 'based on http://www.mvps.org/dmcritchie/excel/formula.htm#hasformula cf_NotFormula = Not cell.HasFormula And Not IsEmpty(cell) _ And Not cell.Row = 1 End FunctionExample:=cf_NotFormula(A1)
would be equivalent to:=AND(NOT(HASFORMULA(A1)),NOT(ISBLANK(A1)),NOT(ROW(A1)=1))
Cells with Formulas – without using a User Defined Function: (#getcell)
Ian Brown (20011110)(better is Bernd Plumhoff 20050929 and Get.Cell ref])
Here’s a way to do it without using VBA!
Select cell A1 on any sheet in the workbook,
Define the name CellHasFormula (using Insert  Name  Define) as
=GET.CELL(48,!A1) –
In Format  Conditional Formatting use a “Formula Is” setting of
=CellHasFormula 
to trigger the defined formats.
Compare to Corresponding Cell on another sheet: As described at the
top of this page Conditional Formatting cannot refer to another sheet. But there
is a way around this using a defined name.
 Select cell A1 on Sheet2, then key in "sht2" into the name box left of the formula bar.
 On Sheet1, Select the area to be checked for correspondence to sheet2, Cell A1 must be the active cell.
 Format, Conditional Formatting, Condition 1 Formula is:
=A1=OFFSET(sht2,ROW()1,COLUMN()1)
Consecutive Duplicates: To compare value in column A with value below, but not include the difference being blank or empty. Select ALL cells then enter
the following conditional formatting formula:
=AND($A1<>"",OR($A1=$A2,OFFSET($A1,1,0)=$A1))
Contains P.O. Box, or variations POBox, Post Office Box, PO Box, and others
(wildcard usage), Jason Morin in Worksheet.Functions
=NOT(ISERROR(SEARCH("*P*O*Box*",A1)))  SEARCH is not case sensitive
Contains "a" anywhere in the cell
=FIND("a",A1)  See strings page is case sensitive
FIND(find_text,within_text,start_num)
=NOT(ISERROR(SEARCH("a",A1)))  SEARCH is not case sensitive but generates an error if not found
Contains "a" anywhere in the row, requires a Helper Column in this case column N
N1: =SUMPRODUCT(LEN($A1:$M1)LEN(SUBSTITUTE($A1:$M1,"a","")))  See strings page
Date in Column A is checked as a valid date. (#moredates)
There is no
specific test for a date, but you can check that the
number falls within a range of perhaps 36161 and 73051:
=AND(A1>DATE(1999,1,1),A1<DATE(2100,1,1))
Date in Column B is at least 30 days ago, highlight the rows. Select All cells with any cell in row 1 as active cell
=IF(AND(ISNUMBER($B1),$B1 < (TODAY()  29)),TRUE,FALSE) – True, False can be defaulted
Date in Column B is in the current week (weeks beginning on Sunday). Select All cells with any cell in row 1 as active cell,  Myrna Larson, 20030701 (#currentweek)
=AND($B1>(TODAY()WEEKDAY(TODAY())),$B1<=(TODAY()WEEKDAY(TODAY())+7))
Since each part is a true or false you can alternatively code this as
=($B1>(TODAY()WEEKDAY(TODAY())) * ($B1<=(TODAY()WEEKDAY(TODAY())+7))
Date in Column A is a Sunday [Sun=1, Mon=2,...,Sat=7]. Select A1 as active cell
=WEEKDAY($A1)=1
=OR(WEEKDAY($A1)=7,WEEKDAY($A1)=1)  Saturday or Sunday
Dates as in Holidays. Enter with the Date function using OR, or
use a table of dates from another sheet. You have to use a named range
if your table is on another sheet. i.e. HolidayTable 'Holidays'!$A$1:$A$15
=OR(B1=DATE(2004,01,01),...,B1=DATE(2004,12,25))
=ISNUMBER(MATCH(B1,HolidayTable,0)) Paul (20040129)
Dates, days to next Birthday or Anniversary less than or equal to 30 days (#anniversary)
Days until upcoming Anniversary (Birthday)  zero if same date
=IF(TODAY()<=DATE(YEAR(TODAY()),MONTH($B1),DAY($B1)), DATE(YEAR(TODAY()),MONTH($B1),DAY($B1))TODAY(), DATE(YEAR(TODAY())+1,MONTH($B1),DAY($B1))TODAY())
Anniversary Date coming up within 30 days. (True or False)
=IF(TODAY()<=DATE(YEAR(TODAY()),MONTH($B1),DAY($B1)), DATE(YEAR(TODAY()),MONTH($B1),DAY($B1))TODAY(), DATE(YEAR(TODAY())+1,MONTH($B1),DAY($B1))TODAY())<=30
(Additional Date examples in more detail above)
Duplicate Lastname in column with “Lastname, Firstname” (wildcard usage)
Peo Sjoblom 20020506
=COUNTIF(A:A,"="&LEFT(A1,FIND(",",A1))&"*")>1
Duplicated Anywhere in Column: Need not be sorted (includes first of duplicates)
Formula is: =COUNTIF($A:$A,$A1)>1
which, of course, is similar to using a cell in another column
to convey the same information:
=IF(COUNTIF($A$1:$A1,$A1)=1,"","Duplicate")
formula 1 is: formula 2 is: Helper Column to show the row number of the original


Duplicate of a Previous Entry in Column: Need not be sorted (does not include first of duplicates)
Formula is: =COUNTIF($A$1:$A1,$A1)>1
Exact Comparison case sensitive
=EXACT(A1,"McRitchie")
Find value in any cell on the row (also see wildcard
=COUNTIF($A1:$K1,"apples")
FIND string in Cell A1 anywhere in cells of Column A
=AND(SEARCH($A$1,$A1),ROW()>1,$A$1<>"") – case insensitive allows
wildcards
=AND(FIND($A$1,$A1),ROW()>1,$A$1<>"") – case sensitive
Highest 4 numbers in a range – can result in
duplicate numbers, if 4th is tied with
5th highest (5th is equal to 4th)
=AND(ISNUMBER($B2),$B2>=LARGE($B$2:$B$200,4))
Highest 4 numbers in a range – can result in
missing numbers, if 4th is tied with 5th
highest (4th is NOT greater than 5th)
=AND(ISNUMBER($B2),$B2>LARGE($B$2:$B$200,5))
Highest Percentile in a range – i.e. top 20%
from a posting by DNF Karran 20040911
=A6>=PERCENTILE(A$6:A$15,0.8)
Looks Blank or ISBLANK – the equivalent VBA formula is ISEMPTY
=TRIM(A1)=""  looks blank
=ISBLANK(A1) – actually is blank, no constants no formulas
=TRIM(A1)<>""  does not look blank
=NOT(ISBLANK(A1)) – actually not blank, may contain formulas
Locked Cells (Cell Protection)
=CELL("protect",A1)>0 – Dave Peterson, worksheet.functions, 2000/06/02
Minimum and Maximum in a specific column
=$C1=MIN($C:$C)  format as bold Red text
=$C1=MAX($C:$C)  format as bold Blue text
 Because of the absolute column, you can color the entire row by selecting all cells on the sheet –
Minimum and Maximum in each of specific columns – preselect columns i.e. C:H
=C1=MIN(C:C)  format as bold Red text
=C1=MAX(C:C) – format as bold Blue text
 Remember the formula used depends on the active cell. 
Not found in a specific range
=COUNTIF(NamedRange,$B2)=0
or the opposite found at least once in a list 
=COUNTIF('sheet1'!NamedRange,$A1)>=1
 Remember the formula used depends on the active cell. 
Number in Column B is Greater Than or Equal to 80
=AND($B1>=80,ISNUMBER($B1))
Number in any Column is Less Than zero – negative numbers exist in row
=COUNTIF(1:1,"<0")>0
Number prefix to a task begins with (text begins with)... color ranges (15, 611, 12&Up) may be like 1., 1a., 1b. 10., 10a, etc.
If A2 is the activecell when entering Conditional Formatting
Condition 1: =VALUE(LEFT(A2,2))>11
[Green]
Condition 2: =OR(LEFT(A2,2)="10",LEFT(A2,2)="11",VALUE(LEFT(A2,1))>5)
[yellow]
Condition 3: =VALUE(LEFT(A2))>0
[Red]
Row is colored, if word “total” appears anywhere within any cell on the row (wildcard usage). (wildcards).
Ctrl+A, with A1 or any cell on row 1 selected
(Ctrl+A is foobared in Excel 2003 hit Ctrl+A twice)
=COUNTIF(1:1,"*total*") – Jason Morin, worksheet.functions, 20030801
Finding each of several numbers in a table , Debra Dalgleish. The example involved 100 people named in A2:A101 each with six numbers in columns B:H. The numbers to be checked for are entered in $J2:$J$100. To enter the Conditional Format, first select B2:H101 then for C.F. use formula is: =ISNUMBER(MATCH(B2,$J$2:$J$100,0)) and select a color on the Pattern tab. Be sure to use absolute references for the arguments in J2:J100 in the C.F.
=TRIM(A1)="single"
=SEARCH("anymatch",A1)
=SEARCH("* anymatch *"," " &$A1 & " ")
– which is just as effective as
=ISNUMBER(SEARCH("* anymatch *"," " &$A1 & " "))
– since "#VALUE!" is treated as False (Not True) in C.F.
=EXACT("McRitchie",A1)
=COUNTIF(1:1,"fail")
– string appears by tself
=COUNTIF(1:1,"*fail*")
– embedded in any cell
Remember the formula used is based on the active cell, and coloring or other formatting is based on the cells, or columns selected.
To identify which cells have conditional formatting, you can select all cells (Ctrl+A, note foobared in Excel 2003 hit Ctrl+A twice), Edit, GoTo (Ctrl+G), conditional formatting.Without On Error you will get #Value! if there is no conditional formatting.GetFormula is used to show normal cell formulas (see #row example on the page), and to show the condition used Conditional Formatting you can use the following user defined funciton.
=CondFormula(D14, 1) – for the 1st of 3 possible conditions
=CondFormula(D14, 2) – for the 2nd of 3 possible conditionsFunction CondFormula(myCell, Optional cond As Long = 1) As String 'Bernie Deitrick programming 20000218, modified D.McR 20010807, 20020207 Application.Volatile CondFormula = "" On Error Resume Next CondFormula = myCell.FormatConditions(cond).Formula1 End Function
Example:
Column A set up for conditional formatting
Formua is: $A1 > 3
G1: =CondFormula(a1,1)
H1: =CondFormula(a1,2)
I1: =CondFormula(a1,3)
And then this still isn’t much of a start if you want to know if the Conditional Formatting actually applies and want to know what resulting coloring or other formatting actually was applied.
If you used A1>3 you would see G1>3 in your G1 cell, so there are still some quirks in this.
Keep in mind that the formula is used and is seen as based on the active cell, so the conditional formula is unique to each cell because each cell is checked individually. Also see John Walkenbach's “The elusive Formula1 property for conditional formatting” as one of his Excel Oddities.
Some people will extend normal/custom cell formatting and Conditional Formatting afterwards,
with the fill handle or the format painter,
but you are much better off (more efficient) doing both normal cell formatting and
Conditional Formatting ahead of time for
the range needed (as an entire column) rather than one cell or row at a time.
The Table in the following example was created via xl2html
macros and handles colors including via Conditional Formatting.
This example shows RED if maximum in Row & Column: otherwise, GREEN if maximum in Column, or
BLUE if maximum in Row

Format, Conditional Formatting
Select B3:I10, with B3 as the Active Cell test file workbooks/xl2html.xls

You can make up you table without use of a Pivot Table as in the following Example:
Based on a posting by unidentified poster (eagle784) on 20050819
A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z  AA  
1  Date \ Hour  00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23  Logged IN  
2  20050701  2  0  0  0  0  0  0  0  0  0  0  0  0  0  0  1  0  8  0  0  0  0  0  0  20050701 00:15  
3  20050702  0  0  0  3  0  0  0  0  0  0  0  0  0  0  1  3  0  0  0  0  0  0  0  0  20050701 00:15  
4  20050703  0  0  0  0  0  1  0  0  0  0  0  0  0  1  0  0  0  0  17  0  0  0  0  0  20050703 05:15  
5  20050704  0  0  0  2  2  3  2  3  0  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0  20050704 03:15  
6  20050705  0  0  0  0  0  0  0  0  0  0  0  1  0  0  0  0  0  0  0  0  0  0  0  0  20050704 03:42  
7  20050704 04:09  
8  B2: =SUMPRODUCT((DAY(Logged_in)=DAY($A2)), (TIME(HOUR(Logged_in),MINUTE(Logged_in),0)>=TIME(B$1,0,0)), (TIME(HOUR(Logged_in),MINUTE(Logged_in),0)<=TIME(B$1,59,59))) 
20050704 04:36  
9  20050704 05:03  
10  20050704 05:30  
11  20050704 05:57  
12  Conditional Format with cell B2 as active cell for range B2:Y6 is =AND(B2>0,B2=MAX($B2:$Y2)) 
20050704 06:24  
13  20050704 06:51  
14  20050704 07:18 
If you have more than 3 sets of conditions then you probably want to use an EVENT macro.
Hopefully this provided sufficient information to get you started. It is a bit tricky at first. Note the mixed reference $D1 meaning that on row 1 only the $H column will be checked. This example is assuming that your active cell is on row 1 when you start.
If you do not want to color the entire row but only columns B, G:I for instance then only select those columns before you start doing your Conditional Formatting. It will be harder to find any of your 3 Conditional Formatting though (warning). See function later on this page.
It is easier to work with Conditional Formatting if you always work form all cells selected if you can work with having an entire row selected. However there are functions and subroutines to help you find them see my [sumdata.htm page. – This will be included here.]
VALUE  =GetFormat(A...)  =GetFormula(A...)  +  
A  B  C  D  
11  Yes  [Red][>0]"No";[Green]"Yes"  =1  F 
12  No  [Red][>0]"No";[Green]"Yes"  =1  F 
13  5.00 
[Blue][>=5]0.00;[Red][<2]0.00;[Yellow] General;[magenta]"Text:"@ 
=5  F 
Here is an example using the "Webdings 2" font.
A  B  C  D  E  F  
2  wanted  value  =B  Format  
3  green/circle  1  Ä  [Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"  
4  orange/asterisk  2  ã  [Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"  
5  red/block  3  ¢  [Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"  
6  any text entry  A  ³  [Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"  
7  
8  wingdings 2  X  =  =  code: Alt+nnnn from numeric keypad  
9  Ä  Ä  Ä  0196  Ä  
10  ³  ³  ³  0179  ³  
11  ¢  ¢  ¢  0162  ¢  
12  ã  ã  ã  0227  ã  
13  Ì  Ì  Ì  0204  Ì  
14  Ó  Ó  Ó  0211  Ó  
15  G  G  G  0071  G  
16  H  H  H  0072  H  
17  I  I  I  0073  I  
18  Color  50  46  Red  
19  
20  Colors  http://www.mvps.org/dmcritchie/excel/colors.htm  
21  symbols  http://www.mvps.org/dmcritchie/rexx/htm/symbols.htm 
The following table and additional information can be found on my Colors page.

(Black, White, Red, Green, Blue, Yellow, Magenta, and Cyan). 
More reading with pictures on conditional formatting.
Conditional Formatting will override the text colors that can be produced for numeric values by normal cell formatting.
Conditional
Formatting, David Hager’s "Excel
Experts ELetter Archives" letters 3, 8, and 14. Letter #8
includes a Narrative
on Conditional Formatting in a Word Document.
(some techniques for those still on XL95 can be found at John Walkenbach's
tip 25). tips
Conditional Formatting, Chip Pearson’s site, also look for conditional on his topic.htm page – http://www.cpearson.com/excel/topic.htm, also used for Color Banding.
Conditional Formatting Debra Dalgleish, a short series of articles.
Some minor examples of Conditional Formatting seen on my pages.
Summarizing Data, and Auto Filter also see the Related area on that page, which does relate to additional examples.
Sub simcondfmt() 'Tom Ogilvy, programming, 20040117 simulate conditional formatting 'color cell in in column B if less than cell in Column K 'Classic case for Conditional Formatting, except that Column K ' is to be deleted later on, so must use a macro. 'Colorindex choices see http://www.mvps.org/dmcritchie/excel/colors.htm Dim rng As Range, cell As Range Set rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) For Each cell In rng 'offset from column B to K is 9 columns If cell.Value < cell.Offset(0, 9).Value Then cell.Font.ColorIndex = 3 Else cell.Font.ColorIndex = xlAutomatic End If Next End Sub
Public Sub ApplyCF() Dim rOldSelect As Range 'J.E.McGimpsey 20040612 Dim rOldActivate As Range Set rOldSelect = Selection Set rOldActivate = ActiveCell Range("B:P").Select Range("B1").Activate With Selection.FormatConditions .Delete .Add _ Type:=xlExpression, _ Formula1:="=IF(B1<>"""",B1<TIME(0,6,10))" .Item(1).Interior.ColorIndex = 10 'Dark Green .Add _ Type:=xlExpression, _ Formula1:="=IF(B1<>"""",B1<TIME(0,7,11))" .Item(2).Interior.ColorIndex = 6 'Yellow .Add _ Type:=xlExpression, _ Formula1:="=if(B1<>"""",B1<1)" .Item(3).Interior.ColorIndex = 3 'Red End With rOldSelect.Select rOldActivate.Activate End SubRegular macro providing similar facility, additional coloring could be added, but you have to run the macro again, if any values are changed. Macros modified to not color column A which might be for descriptions. (modifed from what was asked). Phone calls lasting at least 7 min, 11 secs would be colored Red, calls lasting at least 6 min, 10 secs would be colored Yellow, and calls greater than or equal to zero would get colored Green. Wasn’t written as an Event Macro because it sounded like a one time thing per worksheet, so simply run macro once.
Sub color_calls() 'David McRitchie, 20040612, not posted Dim cell As Range Cells.Interior.ColorIndex = xlNone For Each cell In Intersect(Columns("B:P"), _ Cells.SpecialCells(xlConstants, xlNumbers)) cell.Value = cell.Value Select Case cell.Value Case Is >= TimeSerial(0, 7, 11) cell.Interior.ColorIndex = 3 'red Case Is >= TimeSerial(0, 6, 10) cell.Interior.ColorIndex = 6 'Yellow Case Is >= 0 cell.Interior.ColorIndex = 4 'Green Case Else cell.Interior.ColorIndex = xlNone End Select Next cell End SubFormatConditions – Conditional Formatting – macros, some additional links for FormatConditions in macros found in newsgroups and web pages.
Older articles archived on former Deja are now available back to Mar 29, 1995. Between May 2000 and mid April 2001 things had been bad with articles before 19May1999 missing since before 9July2000, see my Newsgroups page.
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997  2005, F. David McRitchie, All Rights Reserved