Conditional Formatting

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.

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)
 

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<>"")

Removal of Conditional Formatting (#removal)

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, OK

From VBA use:  selection.formatconditions.delete

Coloring date in a column based on today’s date – TODAY() (#date)

Another date example

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 fill-handle 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)

Color Row based on value in a Column (#row)

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.

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.

Multiple formatting (#multiple)

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]

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")>=4

Select columns: C, E, G, I for condition 2:
    format --> Conditional Formatting -->
        Formula2 is: ="completed"

Color Banding     (#colorband)

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

A B C 1 Prod.  Color  Price  2 AB0010 Black 13.50 3 AB0011 White 13.75 4 AB0012 Yellow 14.00 5 AB0013 Green 14.25 6 AB0021 Black 14.50 7 AB0022 White 14.75 8 AB0023 Yellow 15.00 9 AB0024 Green 15.25 10 AB0035 Black 15.50 11 AB0036 White 15.75 12 AC0010 Black 16.00 13 AC0011 White 16.25

A B C 1 Prod.  Color  Price  2 AB0010 Black 13.50 6 AB0021 Black 14.50 10 AB0035 Black 15.50 12 AC0010 Black 16.00 16 AC0021 Black 14.00 20 AC0035 Black 15.00 24 AD0010 Black 15.50 28 AD0021 Black 13.75 32 AD0035 Black 14.75   The above table is a representation of the results of Data, Filter, Auto Filter.  For more information on auto filtering in this example see my sumdata.htm page.

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

Color Grouping with alternating colors (#grouping)

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.

More Notes on Conditional Formatting (#notes)

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 Function

Alternative:  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: [2004-11-04]

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 Function

Example: =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 (2001-11-10)(better is Bernd Plumhoff 2005-09-29 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, 2003-07-01 (#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 (2004-01-29)

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 2002-05-06
=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")
 

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 2004-09-11
=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 (1-5, 6-11, 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, 2003-08-01

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.

Text formulas   (#text)

Remember the formula used is based on the active cell, and coloring or other formatting is based on the cells, or columns selected.

Finding Conditional Formatting Formulas afterwards   (#identify)

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.

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 conditions

Function CondFormula(myCell, Optional cond As Long = 1) As String
  'Bernie Deitrick programming 2000-02-18, modified D.McR 2001-08-07, 2002-02-07
  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.
 

Preparation of examples, HTML Tables, on this page (#tables)

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

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 2005-08-19

Conditional Formatting / Normal Formatting / Event macros (#extend)

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

Normal Cell Formatting   (#normal)

Here is an example using the "Webdings 2" font. 

The following table and additional information can be found on my Colors page.

More reading with pictures on conditional formatting.

1. Conditional Formatting will override the text colors that can be produced for numeric values by normal cell formatting.

2. Conditional Formatting, David Hager’s "Excel Experts E-Letter 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

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

4. Conditional Formatting Debra Dalgleish, a short series of articles.

Some minor examples of Conditional Formatting seen on my pages.

1. Summarizing Data, and Auto Filter also see the Related area on that page, which does relate to additional examples.

2. Coloring within Ranges, Examples

3. Color Palette – the 56 Excel Colors

4. Sorting on Color

Some notes on Filtering

Simulated Conditional Formatting (#simulated)

Sub simcondfmt()
  'Tom Ogilvy,  programming, 2004-01-17 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

Macro creating 3 Conditional Formats (#macro)

Public Sub ApplyCF()
  Dim rOldSelect As Range 'J.E.McGimpsey 2004-06-12
  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 Sub

Sub color_calls()
'David McRitchie, 2004-06-12, 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 Sub

Problems/Restrictions   (#problems)

• 215783 - XL2000: "Excel could not save all the data and formatting" Error
  error message: Excel could not save all the data and formatting you recently added to filename.xls
  This error message appears when you have applied conditional formatting to more than 2,050 rows.
• Define or apply new conditional formats don't work.  However, you can see the effects of conditional formats applied before you shared the workbook. (Help ref: Limitations of shared workbooks)

Related Areas   (#related)

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.

• Alternate-Row Shading in XL97 and above using filters. : =MOD(ROW(),2)=0   J.Walkenbach
• Alternate-Row Shading using a macro
• Alternating fill-colors when Column A changes.  Solution using a helper column and a seed.
    B2:  =IF(A2=OFFSET(A2,-1,0),OFFSET(B2,-1,0),MOD(OFFSET(B2,-1,0)+1,2))
• [broken link] AutoFilter, Display AutoFilter Criteria in Excel
  Steve Bullen’s code to show the criteria actually in use when Autofilter is in Effect.  Article in PC World also has information on use of RANK when tied, and use of alternating stripes via conditional formatting.
• AutoFilter, Find Excel 2000 Data Quickly With AutoFilter -- MS Office, Update
• Chart Formatting Techniques and Tricks, Jon Peltier, Conditional Formatting for your Chart.  Also see AJP - Conditional line chart, Andy Pope, Conditional line chart displays a line that changes color depending on whether it is above or below a control line.
• Color Palette – the 56 Excel Colors
• Color Stripes, Range, Conditional Formatting, Banding, Greenbar stripes (also see autofilter)
  An example of coloring lines when the value in Column D (4th column) is negative. Some information on ranges. A quick look at conditional formatting available with XL97 and later.
• Color Banding With Conditional Formatting (greenbar, colorband) also separate topics C.F., and it’s subtopic C.F., Using cells on other sheets.  -- Chip Pearson.  Some specific examples spelled out by Ken Wright.
• Color-Matching Blues  [http://www.zdnet.com/pcmag/issues/1507/pcmg0103.htm]-- f this link fails to work then search on the title.
• Combine rows, based on Column A posted by Patrick Molloy combines B column as comma separated values for rows that have same column A value (merging cells then deleting rows).
• Conditional Formatting, David Hager’s "Excel Experts E-Letter 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).
• COUNT, Tip 52:Cell Counting Techniques
  range using: DCOUNT, COUNT, COUNTA, COUNTBLANK, COUNTIF, and includes one (wildcard usage) ...
      =COUNTA(D$2:D$500-COUNTIF(D$2:D$500,"y*")-COUNTIF(D$2:D$500,"n*")
      =COUNTA(A2:A100)-SUM(COUNTIF(A2:A100,{"yes","y","no","n"}))
      count of nonblank cells - cells beginning with y - cells beginning with n
      =COUNTA(A2:A100)-SUM(N(A2:A100={"yes","y","no","n"})) – array formula Alt+Enter
  Also see  Summing and Counting Using Multiple Criteria (tip 74)
  And an article for Microsoft Office – Count and Sum Your Data in Excel
• Duplicates, includes the best example duplicates frame from this page (seen above), the new page is shorter and can be used by those already familiar with C.F. or can be used as a reference back to here.
• Extended Conditional Formatter «Excel addin«, Bob Phillips & Frank Kabel, addin that makes it possible to enter up to 50 conditions with the same entry type panels as builtin Conditional Formatting.  Note this is Beta software (testing not production).  (posted 2004-11-07)
• Excel Function Dictionary « by Peter Noneley is a reference to definitely download and maintain on your computer, the workbook has 157+ sheets each with an explanation and example of an Excel function.
• Fill Handle, formulas written so that they can be extended with the fill handle are similar to what you have to use in Conditional Formatting and in Data Validation.
• Font, 6 conditional font colors without macros, John McGimpsey, combining regular cell number formatting and C.F.
• Hide or Highlight Duplicates, contextures.com, Conditional Formatting
• Number Formatting, normal cell formatting should not be overlooked, it is easy to get carried away with Conditional Formatting that one loses track of the obvious.  Also see format, “About Worksheet Formatting” in HELP.
• Pivot Tables, in addition to Harald Staff’s excellent Introduction To Pivot Tables already mentioned. 
  • Microsoft Excel PivotTable Resource Center
  • Tushar Mehta http://www.tushar-mehta.com in his RDBMS (Relational Database) area demonstrates relational database and then creates a Pivot table from the data base.  Suggest going through the RDBMS Tutorial without annoying frames.
• SUMIF Examples
• SUMIF, Tip 74:Summing and Counting Using Multiple Criteria
  using SUMIF, COUNTIF, SUM that should solve most counting and summing problems. -- John Walkenbach

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2005,  F. David McRitchie,  All Rights Reserved