Conditional Formatting

Location: http://www.mvps.org/dmcritchie/excel/condfmt.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

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.

Conditional Formatting prior to Excel 2007: format, conditional formatting
Conditional Formatting in Excel 2007:  Home, Styles, Conditional formatting, Manage Rules

Adding Conditions
  There is a limit of three.
  The first of the 1 to 3 conditions
    that matches will be used.
The limit of 3 applies to each cell that might be colored by a C.F.
The limit of 3 does not apply directly to the sheet but to each cell that might be colored by a specific C.F.  So if you color entire rows based on a condition then each cell in those rows then gets 1 additional Conditional Format applied. 

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.

disambiguation:  (refer to "specification limits" for your versiom of Excel)
Excel 2010 Performance: Performance and Limit Improvements
Conditional formats per cell -- Increased from 3 to available memory.
Unique cell styles (formatting) in a workbook -- Increased from 4,000 to 64,000. (or was that the upgrade to 2007 needs clarification)
Unique colors per workbook -- Increased from 56 to 4.3 billion."
http://msdn.microsoft.com/en-us/library/ff700514.aspx

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).;

[video]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)

Formulas are based on the active cell when entering the formula.  $B1 is used in the formulas so that you can color any columns you want based on the content of column B.  Cell B1 would be the active cell, but because $B1 is used actually any cell on Row 1 could be the active cell when entering the formulas.
 
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)
=$B1=TODAY()
format font is black and bold

condition 2    Formula is:  (within last 8 days)
=AND($B1<TODAY(),$B1>TODAY()-8)
format: font is red and italic

condition 3    Formula is:  (within next 8 days)
=AND($B1>TODAY(),$B1<TODAY()+8)
format font is blue and italic

   
 ABC
1 Date Testing  
2 03/16/2001  
3 03/17/2001  
4 03/18/2001  
5 03/19/2001  
6  03/20/2001 
7  03/21/2001 
8  03/22/2001 
9  03/23/2001 
10  03/24/2001 
11  03/25/2001 
12  03/26/2001 
13 03/27/2001  
14  03/28/2001 
15  03/29/2001 
   
 ABC
1 Date Testing
16  03/30/2001 
17  03/31/2001 
18  04/01/2001 
19  04/02/2001 
20  04/03/2001 
21 04/04/2001  
22 04/05/2001  
23 04/06/2001  
24 04/07/2001  

Another date example

The first example will highlight numbers with green interior coloring that are equal to today’s date (ran this on Sept 18, 2000 US date format).
  =TODAY()=$B1

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

 BCD
509/16/2000FALSED5-1
609/17/2000FALSED6-1
709/18/2000 FALSED7-1
809/19/2000FALSED8-1
rows 9-15 ~~~
1609/17/2001FALSED16-1
1709/18/2001 TRUED17-1
1809/19/2001 TRUED18-1
1909/20/2001 TRUED19-1
20 FALSED20-1
 
 BCD
2109/22/2001 TRUED21-1
2209/23/2001 TRUED22-1
2309/24/2001 TRUED23-1
rows 24-28 ~~~
2909/30/2001 TRUED29-1
3010/01/2001 TRUED30-1
31 FALSE 
32 FALSE 
33   

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.

 

 ABCDE
1description Col(B)Col(C) =B/C=GetFormula(Dn)
2description 1 33 200 0.165 =IF(ISERROR(B2/C2),"",B2/C2)
3description 2 500 475 1.053 =IF(ISERROR(B3/C3),"",B3/C3)
4description 3 105 100 1.050 =IF(ISERROR(B4/C4),"",B4/C4)
5description 4 -5 4 (1.250) =IF(ISERROR(B5/C5),"",B5/C5)
6description 5 560 400 1.400 =IF(ISERROR(B6/C6),"",B6/C6)
7description 6 75 160 0.469 =IF(ISERROR(B7/C7),"",B7/C7)
8description 7 0 1 0.000 =IF(ISERROR(B8/C8),"",B8/C8)
9description 8     =IF(ISERROR(B9/C9),"",B9/C9)
10description 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.

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]
 ABCDEFGHI
1Sample 5Test 1 3Test 2 10Test 3 3Test 4
210145 Completed2 2 of 310 Completed2 2 of 3
31015 5 Completed 3 Completed 10 Completed 3 Completed
410164 4 of 5  of 3  of 10  of 3
510175 Completed3 Completed9 9 of 103 Completed
61018 5 Completed 3 Completed 10 Completed 3 Completed
710191 1 of 5  of 3  of 10  of 3
81020   of 5   of 3   of 10   of 3
910215 Completed3 Completed10 Completed2 2 of 3
101022   of 5   of 3   of 10   of 3
11  nnn nnnnnnnnnnnnn nnn nnnnnnnnnnnnn nnn nnnnnnnnnnnnn nnn nnnnnnnnnnnnn
12C2:  =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")>=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

 
Conditional Format:   =MOD(SUBTOTAL(3,$A$1:$A1),3) to color every 3rd row
 A B C
1Prod.  Color  Price 
2AB0010Black 13.50
3AB0011White 13.75
4AB0012 Yellow 14.00
5AB0013Green 14.25
6AB0021Black 14.50
7AB0022 White 14.75
8AB0023Yellow 15.00
9AB0024Green 15.25
10AB0035 Black 15.50
11AB0036White 15.75
12AC0010Black 16.00
13AC0011 White 16.25
   
 A B C
1 Prod.  Color  Price 
2AB0010 Black 13.50
6AB0021 Black 14.50
10 AB0035Black 15.50
12AC0010 Black 16.00
16AC0021 Black 14.00
20 AC0035Black 15.00
24AD0010 Black 15.50
28AD0021 Black 13.75
32 AD0035Black 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)

 
 ABCDE
 1 Lastname  Firstname  Child Grade  0 
 2  Smith Aaron  Alice 5   1 
 3  Smith Aaron  Susan 8   1 
 4 Smith Harold  Douglas 3   0 
 5  Taylor Paul  Alice 10   1 
 6 Taylor Tom  Chris 7   0 
 7  Ulmer Tom  David 8   1 
 8 Ulmer Sandy  Fred 4   0 
 9 Ulmer Sandy  Sandy 12   0 
  
 ABCDE
 1  Lastname   Firstname   Child  Grade  0 
 2   Smith  Aaron   Alice  5    1 
 3   Smith  Aaron   Susan  8    1 
 4  Smith   Harold  Douglas  3    0 
 5        2 
 6        2 
 7   Taylor  Paul   Alice   10    1 
 8        3 
 9  Taylor  Tom   Chris  7    0 
10        2 
11   Ulmer  Tom   David  8    1 
12   Ulmer  Tom   David  8    1 
13   Ulmer  Tom   David  8    1 
14        3 
15  Ulmer  Sandy   Sandy  12    0 
16  Ulmer  Sandy   Sandy  12    0 
  
 ABCDE
 1 Lastname  Firstname  Child Grade  0 
 2  Smith Aaron  Alice 5   1 
 3  Smith Aaron  Susan 8   1 
 4 Smith Harold  Douglas 3  0 
 5  Taylor Paul  Alice 10   1 
 6 Taylor Tom  Chris 7  0 
 7 Ulmer Tom  David 8  0 
 8  Ulmer Sandy  Fred 4   1 
 9  Ulmer Sandy  Sandy 12   1 
We want to Group anytime there is a Change in Column A or Column B.  Conditional Formatting can not keep track of what the previous color was, so we will have to use a helper column, and then have Conditional Formatting check the helper column.

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)

The key to using Conditional Formatting (C.F.) is to reduce a formula so that it indicates TRUE or FALSE (1 or 0).  C.F. overrides normal cell formatting.  You are limited to the use of three conditional formatting sets per worksheet (like three wishes).  C.F. began with XL97.  For XL95 you would have to write your own Event macros.

 

Here is a nice summary presented by Leo Heuser 2001-07-28, misc.

Please note the use of mixed absolute ($$) and relative (no dollars) references.

  1. Select column B (click the B in the column bar)
  2. Choose Formats > Conditional formatting
  3. With “Condition 1” choose “Formula is”
  4. Enter this formula:  =COUNTIF($B$1:B1,B1)>1
  5. Click the button “Formats” and choose a proper format. OK.OK
  6. In another column, C1, enter this formula:
         =IF(COUNTIF($B$1:B1,B1)>1,"Number exists in row " & MATCH(B1,$B$1:B1,0);"")
  7. Drag C1 down with the fill handle (the little square in the lower right
    corner of the cell)
  
    BCD
1Name Leo’s markingPeo’s marking
2Allen  
3Jack  
4Casey  
5Jack ... exists in row 3... exists in cell B3
6David  
7Casey ... exists in row 4 ... exists in cell B4 
8Jerome   
conditional format:  =COUNTIF($B$1:B1,B1)>1
 
Chip Pearson has specific information on
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 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")
 

(#duplicates)
Duplicate identifications (yellowish first among duplicates, greenish for actual duplicates) 
  formula 1 is:  =IF(COUNTIF($D:$D,$D1)>1,COUNTIF($D$1:$D1,$D1)=1)
  formula 2 is: 
=IF(COUNTIF($D:$D,$D1)>1,COUNTIF($D$1:$D1,$D1)>1)

Helper Column to show the row number of the original

    Identification of the row number for the original value formula anywhere on row 1
    =IF(COUNTIF($D$1:$D1,$D1)>1,MATCH($D1,$D$1:$D1,0),"")

Because of column headers, normally start anywhere on row 2
    =IF(COUNTIF($D$1:$D2,$D2)>1,MATCH($D2,$D$1:$D2,0),"")

     
 
 DE
 1 Code  ID
 2 D2 
 3 D3  
 4 D4 
 5 D3  3 
 6 D6 
 7 07-2  
 8 D8  
 9 D9 
10 D10 
11 D3  3 
 
 DE
 1 Code  ID
12 24-3  
13 D13 
14 D8  8 
15 D15 
16 D3  3 
17 11-1 
18 24-3  12 
19 07-1 
20 07-2  7 
21 24-3  12 

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)

If the word stands by itself (alone) in the cell and is not case sensitive then the formula used might be
  =TRIM(A1)="single"
if the string is embedded in additional text and not case sensitive, you might use
  =SEARCH("anymatch",A1)
if the word is embedded in additional text and not case sensitive, you might use
  =SEARCH("* anymatch *"," " &$A1 & " ")   – which is just as effective as
  =ISNUMBER(SEARCH("* anymatch *"," " &$A1 & " "))   – since "#VALUE!" is treated as False (Not True) in C.F.
If you are looking for a case sensitive Exact match.
  =EXACT("McRitchie",A1)
If you are looking for "fail" anywhere in the entire row (use to color entire row)
  =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.

Finding Conditional Formatting Formulas afterwards   (#identify)

Conditional Formatting Formulas are considerably harder to show and find once used, unless you know which range to pick.  Each Conditional Format has a range that you can’t just see, anyway the following is a start and will work best if it just has a formula, rather than “is less than” type of conditions.
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
Without On Error you will get #Value! if there is no conditional formatting.

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 examples on this page were created with the help of macros found on my xl2html.htm page.  The macros now recognize color changes made by Conditional Formatting and will left/center/right justify cells as used in Excel; nevertheless, the purpose of these macros is to keep the output small and usable so font changes are kept to minimum and the routines are ignorant of HTML coding so HTML can be used in cells to help generate HTML coding.

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

 ABCDEFGHIJ
 1  Sum of Count  hour          
 2  Date 09  10   11  12  13  14  15  16   Grand Total
 3  2005-07-08 Fri 7  3  41   13  32  89  22   82  289 
 4  2005-07-09 Sat 18  80  30   47  84  25  95   72  451 
 5  2005-07-10 Sun 73   98  80  29   94  80  25  51   530 
 6  2005-07-11 Mon 85  64  29   96  1  90   51  45  461 
 7  2005-07-12 Tue 28  6  35   24  25  44  62   44  268 
 8  2005-07-13 Wed 83   33  27  79  19  31  40   52  364 
 9  2005-07-14 Thu 89  79  53   65  22  90  11   36  445 
10  2005-07-15 Fri 91   53  31  76  0  66   99  79  495 
11  Grand Total 474  416  326   429  277  515  405  461   3303 
  Format, Conditional Formatting
Select B3:I10, with B3 as the Active Cell
        test file workbooks/xl2html.xls
   Condition 1    
  Formula Is   =AND(B3=MAX(B$3:B$10),B3=MAX($B3:$I3))  
    AaBbCcYyZz  [ Format ]  
          
   Condition 2    
  Formula Is   =B3=MAX(B$3:B$10) 
    AaBbCcYyZz  [ Format ]  
          
   Condition 3    
  Formula Is   =B3=MAX($B3:$I3) 
    AaBbCcYyZz  [ Format ]  
          
Some similar things: in addition to xl2html described above.

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

 ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1 Date \ Hour0001 02030405 06070809 10111213 14151617 18192021 2223  Logged IN
2 2005-07-01200 000000 000000 10800 0000 2005-07-01 00:15
3 2005-07-02000 30000 000000 13000 00000 2005-07-01 00:15
4 2005-07-03000 001000 000010 000170 0000 2005-07-03 05:15
5 2005-07-04000 2232 30000 100000 000000 2005-07-04 03:15
6 2005-07-05000 000000 00100 000000 0000 2005-07-04 03:42
7 2005-07-04 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)))
2005-07-04 04:36
9 2005-07-04 05:03
10 2005-07-04 05:30
11 2005-07-04 05:57
12 Conditional Format with cell B2 as active cell for range B2:Y6 is
    =AND(B2>0,B2=MAX($B2:$Y2))
2005-07-04 06:24
13 2005-07-04 06:51
14 2005-07-04 07:18
More information on SUMPRODUCT can be found in SUMPRODUCT, Multiple Condition Tests", by Bob Phillips.

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

If you only needed two (possibly 3) font colors for numeric values + one color for text values you may be able to use normal cell formatting.  Normal cell formatting does not support interior (background) coloring.

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)

My Formulas page Show FORMULA of another cell in Excel has additional examples of normal cell formatting under the GetFormat() macro.  These are some of those shown.

 

  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. 

 

 ABCDEF
2wanted  value=BFormat 
3green/circle 1 Ä [Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"
4orange/asterisk  2 ã [Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"
5red/block 3 ¢ [Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"
6any text entry A ³ [Color50][=1]"Ä";[Color46][=2]"ã";[Red]"¢";[Color13]"³"
7 
8wingdings 2X == code: Alt+nnnn from numeric keypad
9  Ä Ä Ä 0196Ä
10  ³ ³ ³ 0179³
11  ¢ ¢ ¢ 0162¢
12  ã ã ã 0227ã
13  Ì Ì Ì 0204Ì
14  Ó Ó Ó 0211Ó
15  G G G 0071G
16  H H H 0072H
17  I I I 0073I
18Color5046 Red  
19       
20Colors http://www.mvps.org/dmcritchie/excel/colors.htm
21symbols http://www.mvps.org/dmcritchie/rexx/htm/symbols.htm

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

1 53 52 51 49 55 56
9 46 12 10 5 47 16
3 45 43 50 41 13 48
7 44 6 4 33 54 15
38 40 36 35 37 39 2
 
Hex equivalents used in HTML
#000000#993300#333300#003300 #003366#333399#333333
#800000#FF6600#808000 #008000 #0000FF #666699 #808080
#FF0000 #FF9900 #99cc00 #339966 #3366FF #800080 #969696
#FF00FF #FFcc00 #FFFF00 #00FF00 #00ccFF #993366 #c0c0c0
#FF99cc #FFcc99 #FFFF99 #ccFFcc #99ccFF #cc99FF #FFFFFF
Excel only recognizes Color 1 through 8 by name
(Black, White, Red, Green, Blue, Yellow, Magenta, and Cyan).

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

You might also consider Data, Filter, ....     to show only the rows you are interested in.  Debra Dalgleish has pages on Filtering such as Excel Filters – Advanced Filter.  Much of what you use in Conditional Formatting applies to Filtering as well.  The drop down list of unique items was increased in Excel 97 and Excel 98 to 1000 from 250. [MSKB 105322].  Additional links to filtering can be found on my sumdata.htm page.

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)

Macro creates Conditional Formatting formulas.  Solution by John McGimpsey 2004-06-12 in programming.  (You can compare it to a regular macro that follows this one.)
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
Regular 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, 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
   FormatConditions – Conditional Formatting – macros, some additional links for FormatConditions in macros found in newsgroups and web pages.

Problems/Restrictions   (#problems)

The Related Area here was copied from my Summarizing Data, and Auto Filter (an Overview) Related Areas since only a few items do not relate to Conditional formatting those items have also been retained.

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.


This page was introduced on November 27, 2000. 
 
[My Excel Pages – home]    [INDEX to my site and the off-site pages I reference] 
[Site Search – Excel]     [Go Back]    [Return to TOP

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


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