查看: 2956|回复: 12
请问函数if(($B$2:$B$15=LEFT(D$1,2))*($C$2:$C$1…
阅读权限30
在线时间 小时
请问这个公式是什意思：IF(($B$2:$B$15=LEFT(D$1,2))*($C$2:$C$15=MID(D$1,3,3)),ROW($2:$15),65536)
我是菜鸟，请给点尽量详细点的解释，先谢谢了！
[ 本帖最后由 yyyydddd8888 于
11:59 编辑 ]
11:59 上传
点击文件名下载附件
3.02 KB, 下载次数: 21
阅读权限100
在线时间 小时
要想详细解答，请传上附件。谢谢！
阅读权限50
在线时间 小时
光标放在公式编辑栏，按F9分段显示公式结果。满足两个条件返回行号，否则返回65536.
阅读权限50
在线时间 小时
得到一组符合条件的行号。
也就是要同时满足$B$2:$B$15=D$1的前两个字符和$C$2:$C$15=D$1的3~5位的三个字符，得到其所在行的行号，不符合条件的均为65536。
阅读权限20
在线时间 小时
如果满足：B2到B15有等于D1单元格中左边2位数值或文本，和C2到C15有等于D1单元个中从第3位（含第3位）3位数值或文本
就显示其行号，如果不满足就显示65536
阅读权限30
在线时间 小时
回复 2楼 laowuwyx 的帖子
对不起，昨天我们这里停电，没有能传上附件，现在我传上一附件。
阅读权限30
在线时间 小时
回复 5楼 jtgyjyy 的帖子
row($2:$15)中返回的值不是2、3、4、5、6、7到15 这几个数字吗？怎么会是同时符合前面两个条件的行数呢？
阅读权限100
在线时间 小时
回复 1楼 yyyydddd8888 的帖子
这是提前取满足条件记录的经典公式，这个公式对初学者来是较为困难，最好的方法是，在公式编辑栏中，逐个抹黑公式的各部分，然后按F9多看几遍，最好直接再写几遍公式，便会慢慢理解的。
阅读权限30
在线时间 小时
回复 8楼 laowuwyx 的帖子
谢谢8楼的教导，我就是在编辑栏里抹黑了看显示的结果。但是就是不明白，为什么ROW($2:$15)这一个公式显示的值是2、3、4、5、6、7、8、9、10、11、12、13、14、15这14个数字，可是为什么加上前面的条件，IF(($B$2:$B$15=LEFT(D$1,2))*($C$2:$C$15=MID(D$1,3,3)),ROW($2:$15)，65539），怎么如果if的前面的条件为真的时候，就应该显示ROW($2:$15)的值啊，也就是2到15的这十四个数字啊，怎么它为真的时候显示的是其中的一部分数字2、10、12、14呢？为假的时候显示65536这个我是明白的，就是对于条件为真的时候，显示的结果有点不明白。还有就是在F栏里面，我想应该small函数所要的K值就从1到14的数字就行了，也就公式中=INDEX($A:$A,SMALL(IF(($B$2:$B$15=LEFT(F$1,2))*($C$2:$C$15=MID(F$1,3,3)),ROW($2:$15),65536),ROW(C1)))&&&最后的ROW(C1)改成ROW(A1)也应该可以的啊，可是我改了以后怎么显示的就是空白了呢？而且向下拖的时候就显示的是#NUM!这样的错误提示。请高手帮我讲解讲解吧。先谢谢你了。
12:29 上传
点击文件名下载附件
3.02 KB, 下载次数: 16
阅读权限90
在线时间 小时
回复 9楼 yyyydddd8888 的帖子
你应该把你的问题一个一个罗列出来，而不是写一大段话！
1。为什么ROW($2:$15)这一个公式显示的值是2、3、4、5、6、7、8、9、10、11、12、13、14、15这14个数字？
A：这就是提到2到15行的行号
2.怎么它为真的时候显示的是其中的一部分数字2、10、12、14呢？
A：显示一部分数值，这些就是满足条件的行号，用SMALL结合INDEX从小到大一个个把对应结果提取出来
3.也就公式中=INDEX($A:$A,SMALL(IF(($B$2:$B$15=LEFT(F$1,2))*($C$2:$C$15=MID(F$1,3,3)),ROW($2:$15),65536),ROW(C1)))&&&最后的ROW(C1)改成ROW(A1)也应该可以的啊，可是我改了以后怎么显示的就是空白了呢？
A：应该是你没有按数组三键
玩命加载中，请稍候
玩命加载中，请稍候
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！
本站特聘法律顾问：徐怀玉律师 李志群律师
Powered by格式：doc&&&
贡献者：Punchenlu134
上传时间： 10:11
此文档归属以下专题
暂无相关专题
暂无相关文档
-------------
新增文件夹...
(多个标签用逗号分隔)
&EXCEL技巧三十五：OFFSET函数的应用
條件格式是Excel最難的一部分，本文中有較為全面的學習指南。
條件格式是Excel最難的一部分，本文中有較為全面的學習指南。
分享到：&&
下载本文档需要登录，并付出相应积分。()
文件大小:4.51MB
所需积分:& 50
&2006-, All rights reserved.Conditional Formatting
Location: http://www.mvps.org/dmcritchie/excel/condfmt.htm & & &
Home page:
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 .&
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.
, , 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
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
and would have
to specify your 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
needs clarification)
Unique colors per workbook -- Increased from 56 to 4.3 billion."
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.
style, strike through,
and/or color
& border:&&
which borders, limited number of styles, and/or
& patterns (interior):&&
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.&
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
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
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
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
True condition
or a False condition & that is the whole purpose of C.F.&
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}=&&
formula 3 is:
=AND(TRIM(D3)="",
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
(foobared in Excel 2003 hit
& Format, Condition Formatting,
delete button, check all 3 format boxes, OK
From VBA use:&
selection.formatconditions.delete
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.&
B if just coloring Column B,
or select any
combination of columns, or all columns (Ctrl+A
(foobared in Excel 2003 hit
Format --& Conditional Formatting
& & [add format]
for condition 2
& & [add format]
for condition 3
condition 1 &&
Formula is:&
(due today,
current date)
=$B1=TODAY()
condition 2 &&
Formula is:&
(within last 8 days)
=AND($B1&TODAY(),$B1&TODAY()-8)
font is red
and italic
condition 3 &&
Formula is:&
(within next 8 days)
=AND($B1&TODAY(),$B1&TODAY()+8)
font is blue
and italic
1&Date Testing
2&03/16/2001
3&03/17/2001
4&03/18/2001
5&03/19/2001
03/20/2001&
03/21/2001&
03/22/2001&
03/23/2001&
03/24/2001&
03/25/2001&
03/26/2001&
13&03/27/2001
03/28/2001&
03/29/2001&
1&Date Testing
03/30/2001&
03/31/2001&
04/01/2001&
04/02/2001&
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())))
509/16/2000FALSED5-1
609/17/2000FALSED6-1
709/18/2000
809/19/2000FALSED8-1
rows 9-15 ~~~
1609/17/2001FALSED16-1
1709/18/2001
1809/19/2001
1909/20/2001
20&FALSED20-1
2109/22/2001
2209/23/2001
2309/24/2001
rows 24-28 ~~~
2909/30/2001
3010/01/2001
To highlight only in Column B, select column B before
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.
Color Row based on value in a Column (#row)
Select all cells (Ctrl+a) &
(foobared in Excel 2003 hit
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.
1description
Col(B)Col(C)
=B/C=GetFormula(Dn)
2description 1
=IF(ISERROR(B2/C2),"",B2/C2)
3description 2
=IF(ISERROR(B3/C3),"",B3/C3)
4description 3
=IF(ISERROR(B4/C4),"",B4/C4)
5description 4
=IF(ISERROR(B5/C5),"",B5/C5)
6description 5
=IF(ISERROR(B6/C6),"",B6/C6)
7description 6
=IF(ISERROR(B7/C7),"",B7/C7)
8description 7
=IF(ISERROR(B8/C8),"",B8/C8)
9description 8
&=IF(ISERROR(B9/C9),"",B9/C9)
10description 9
=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.&
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
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:
& [interior Color 36 Light Yellow]
&ABCDEFGHI
Completed2
Completed2
Completed3
Completed9
Completed3
Completed10
Completed2
nnnnnnnnnnnnn
nnnnnnnnnnnnn
nnnnnnnnnnnnn
nnnnnnnnnnnnn
=IF(B2&= B$1,"Completed",B2 & " of " &
If you wanted to be more specific with the C.F. you could to something
Select ALL cells
for condition 1:
& & format --& Conditional Formatting --&
& & formula1
is: =SUM($c1="completed",$e1="completed",$g1="completed",$i1="completed")&=4
C, E, G, I
for condition 2:
& & format --> Conditional Formatting -->
="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 .
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
Color& Price&
2AB0010Black
3AB0011White
5AB0013Green
6AB0021Black
8AB0023Yellow
9AB0024Green
11AB0036White
12AC0010Black
Color& Price&
AB0035Black
AC0035Black
AD0035Black
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
Additional information on Color Banding can be found in the Related Area
at the bottom of this page.
Color Grouping with alternating colors (#grouping)
1 Lastname
Child Grade
Smith Aaron
Smith Aaron
4 Smith Harold
Taylor Paul
6 Taylor Tom
8 Ulmer Sandy
9 Ulmer Sandy
&nbspABCDE
1 Lastname
Child Grade
Smith Aaron
Smith Aaron
4 Smith Harold
Taylor Paul
6 Taylor Tom
7 Ulmer Tom
Ulmer Sandy
Ulmer Sandy
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
& 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), $B2OFFSET($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
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
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:&
& & & E2:&
=MOD(OFFSET($E2,-1,0)+ ($A2OFFSET($A2,-1,0)),2)
and use the
to fill down from E2.& Use of
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 .
Here is a nice summary presented by Leo Heuser
Please note the
use of mixed absolute ($$) and relative (no dollars) references.
Select column B (click the B in the column bar)
Choose Formats > Conditional formatting
With &Condition 1& choose &Formula is&
Enter this formula:& =COUNTIF($B$1:B1,B1)&1
Click the button &Formats& and choose a proper format. OK.OK
In another column, C1, enter this formula:
& & &=IF(COUNTIF($B$1:B1,B1)&1,"Number&exists&in&row&" & MATCH(B1,$B$1:B1,0);"")
Drag C1 down with the fill handle (the little square
in the lower right corner of the cell)
Leo&s markingPeo&s marking
... exists in row 3... exists in cell B3
... 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
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!& for instance.&
& & Formula is:& =HasFormula(A1)
Function HasFormula(cell)
HasFormula = cell.HasFormula
for C.Formatting
End Function
Alternative:& You can temporarily see which cells have formulas
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
(Ctrl+A is foobared in Excel 2003 hit
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: []
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 ()(better is Bernd Plumhoff
& 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
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
Some people will extend normal/ cell formatting and Conditional Formatting afterwards,
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
page.& The macros now recognize
color changes made by Conditional Formatting and will left/center/right
justify cells as used in E 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
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
Sum of Count
Grand Total
Grand Total 474
Format, Conditional Formatting
Select B3:I10,
with B3 as the Active Cell
& & & & test file
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
described above.
Subroutines by Chip Pearson,
were used as the basis for adding C.F. to my xl2html coding, so
far Chip has not made corrections to his code that I had to make.
, Allen Wyatt, converts
the C.F. colors and borders to permanent formatting in the Excel file.
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
&ABCDEFGHIJKLMNOPQRSTUVWXYZAA
Date \ Hour0001
&Logged IN
<font color="#FF-07-01&00:15
<font color="#FF-07-01 00:15
<font color="#FF-07-03 05:15
<font color="#FF-07-04 03:15
<font color="#FF-07-04 03:42
<font color="#FF-07-04 04:09
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)))
<font color="#FF-07-04 04:36
<font color="#FF-07-04 05:03
<font color="#FF-07-04 05:30
<font color="#FF-07-04 05:57
Conditional Format
with cell B2 as active cell for range B2:Y6 is & & =AND(B2>0,B2=MAX($B2:$Y2))
<font color="#FF-07-04 06:24
<font color="#FF-07-04 06:51
<font color="#FF-07-04 07:18
More information on SUMPRODUCT can be found in
, 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 .
Hopefully this provided sufficient information to get you
It is a bit tricky at first.&
Note the mixed reference
meaning that on row 1 only the $H column will be
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
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)
Formulas page
has additional examples of normal cell formatting under the GetFormat() macro.&
These are some of those shown.
=GetFormat(A...)
=GetFormula(A...)
[Red][&0]&No&;[Green]&Yes&
[Red][&0]&No&;[Green]&Yes&
[Blue][&=5]0.00;[Red][&-2]-0.00;[Yellow]
G[magenta]&Text:&@
Here is an example using the "Webdings 2" font.&
value=BFormat&
3green/circle&1
[Color50][=1]&?&;[Color46][=2]&?&;[Red]&?&;[Color13]&?&
4orange/asterisk&
[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]&?&
8wingdings 2X
Alt+nnnn from numeric keypad
18Color5046
following table and additional information can be found on my
Hex equivalents used in HTML
#000000#993300#333300#003300
#003366#333399#333333
#800000#FF6600#808000
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.
override the text colors that can be produced for numeric values by normal
cell formatting.
, David Hager&s
letters 3, 8, and 14.& Letter #8
includes a
in a Word Document.
(some techniques for those still on XL95 can be found at John Walkenbach's
, Chip Pearson&s site, also look for conditional on his topic.htm
also used for Color Banding.
Debra Dalgleish, a short series of articles.
Some minor examples of Conditional Formatting seen on my pages.
also see the
on that page,
which does relate to additional examples.
Some notes on Filtering
You might also consider
Filter, .... & & to show only the
rows you are interested in.& Debra Dalgleish has pages on
Filtering such as .&
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
[MSKB 105322].&
Additional links to filtering can be found on
Simulated Conditional Formatting (#simulated)
Sub simcondfmt()
'Tom Ogilvy,
programming,
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
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
cell.Font.ColorIndex = xlAutomatic
Macro creating 3 Conditional Formats (#macro)
Macro creates Conditional Formatting formulas.&
Solution by
in programming.& (You can compare
it to a regular macro that follows this one.)
Public Sub ApplyCF()
Dim rOldSelect As Range 'J.E.McGimpsey
Dim rOldActivate As Range
Set rOldSelect = Selection
Set rOldActivate = ActiveCell
Range("B:P").Select
Range("B1").Activate
With Selection.FormatConditions
Type:=xlExpression, _
Formula1:="=IF(B1&&"""",B1&TIME(0,6,10))"
.Item(1).Interior.ColorIndex = 10
'Dark Green
Type:=xlExpression, _
Formula1:="=IF(B1&&"""",B1&TIME(0,7,11))"
.Item(2).Interior.ColorIndex = 6
Type:=xlExpression, _
Formula1:="=if(B1&&"""",B1&1)"
.Item(3).Interior.ColorIndex = 3
rOldSelect.Select
rOldActivate.Activate
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
because it sounded like a one time thing
per worksheet, so simply run macro once.
Sub color_calls()
'David McRitchie, , 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
Case Is >= TimeSerial(0, 6, 10)
cell.Interior.ColorIndex = 6
Case Is &= 0
cell.Interior.ColorIndex = 4 'Green
cell.Interior.ColorIndex = xlNone
End Select
some additional links for FormatConditions in macros found in newsgroups and web pages.
Problems/Restrictions &
(#problems)
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)
The Related Area here was copied from my
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
in XL97 and above using filters.
: =MOD(ROW(),2)=0 &
J.Walkenbach
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))
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.
-- MS Office, Update
, Jon Peltier, Conditional Formatting for your Chart.& Also see , Andy Pope, Conditional line chart
displays a line that changes color depending on whether it is above or below a control line.
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.
(greenbar, colorband) also separate topics , and it&s subtopic
.& -- Chip Pearson.& Some specific
spelled out by Ken Wright.
[/pcmag/issues/1507/pcmg0103.htm]-- f this link fails to work then search on the title.
posted by Patrick Molloy combines B column as comma separated values for
rows that have same column A value (merging cells then deleting rows).
David Hager&s
letters 3, 8, and 14.&
Letter #8 includes a
in a Word Document.
(some techniques for those still on XL95 can be found at John Walkenbach's
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
And an article for Microsoft Office &
, includes the best example
(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.
&#171;Excel addin&#171;, 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 )
&#171; 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.
, formulas written so that they can
be extended with the fill handle are similar to what you have to use in Conditional Formatting
Font, , John McGimpsey, combining regular cell number formatting and C.F.
, , Conditional 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
already mentioned.&
Tushar Mehta
(Relational Database)
area demonstrates relational database and then creates a Pivot table from the data base.&
Suggest going through the
Tutorial without annoying frames.
using SUMIF, COUNTIF, SUM that should solve most counting and summing problems. -- John Walkenbach
This page was introduced on November 27, 2000.&
,& F.&David McRitchie,& All Rights Reserved