查看: 1213|回复: 6|
在线时间953 小时经验2006 威望0 性别男最后登录注册时间阅读权限70UID300030积分2006帖子精华0分享0
EH铁杆, 积分 2006, 距离下一级还需 1194 积分
积分排行516帖子精华0微积分0
设置比如字体，填充或线条颜色的时候，会用colorindex, 或color, 我想知道如果用color属性，可以有几种内置的颜色可以用，查了一下，没查到相关的说明，只知道有几种是可以的，像vbred,vbblue, vbyellow, vbblack, vbwhite, 不知道还有哪些？
猜你喜欢看
在线时间140 小时经验2088 威望17 性别男最后登录注册时间阅读权限95UID13913积分7938帖子精华11分享0
积分排行102帖子精华11微积分0
使用RGB函数的话,应该有156X256X256种颜色吧.不过,Excel 2003好像只支持56种颜色.而Excel 2007则支持更多的颜色.
Excel博客：
微信号：excelperfect
在线时间953 小时经验2006 威望0 性别男最后登录注册时间阅读权限70UID300030积分2006帖子精华0分享0
EH铁杆, 积分 2006, 距离下一级还需 1194 积分
积分排行516帖子精华0微积分0
以下是引用fanjy在 18:48:13的发言：使用RGB函数的话,应该有156X256X256种颜色吧.不过,Excel 2003好像只支持56种颜色.而Excel 2007则支持更多的颜色.谢版主，这个我知道，我想知道的是，用color属性，可以用的颜色一共有哪几种，就是以vb开头的那种，直接列明颜色名称的，而用colorindex，后面接数字，不太直观，记不太清，
在线时间140 小时经验2088 威望17 性别男最后登录注册时间阅读权限95UID13913积分7938帖子精华11分享0
积分排行102帖子精华11微积分0
VBA帮助中列出的Color常量是8种.
在线时间953 小时经验2006 威望0 性别男最后登录注册时间阅读权限70UID300030积分2006帖子精华0分享0
EH铁杆, 积分 2006, 距离下一级还需 1194 积分
积分排行516帖子精华0微积分0
我就是想知道具体的这几种，因为我的excel是英文版2002的，我也在帮助里面找过，没有找到，能麻烦版主帮我列一下吗？我知道几种，vbred, vbgreen, vbblue, vbwhite,vbblack, vbyellow, 还有两种不知道，
在线时间1340 小时经验13601 威望29 性别男最后登录注册时间阅读权限100UID20291积分15651帖子精华1分享0
管理以下版块
积分排行23帖子精华1微积分0
Color 常数&&&可在代码中的任何地方用下列代替实际值：常数值描述vbBlack0x0黑色vbRed0xFF红色vbGreen0xFF00绿色vbYellow0xFFFF黄色vbBlue0xFF0000蓝色vbMagenta0xFF00FF紫红色vbCyan0xFFFF00青色vbWhite0xFFFFFF白色
Windows7 + Office 2010
在线时间953 小时经验2006 威望0 性别男最后登录注册时间阅读权限70UID300030积分2006帖子精华0分享0
EH铁杆, 积分 2006, 距离下一级还需 1194 积分
积分排行516帖子精华0微积分0
多谢版主，要的就是这个，
积分≥4700即可申请
金牌优秀会员
金牌优秀会员奖章No.1
优秀会员奖章No.1
关注我们，与您相约微信公众平台！
Copyright 1999 - 2017 Excel Home. All Rights Reserved.本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！
Powered by
本站特聘法律顾问：徐怀玉律师 李志群律师 &&温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
阅读(128)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
loftPermalink:'',
id:'fks_',
blogTitle:'[转].net Excel设置颜色font.ColorIndex 各颜色编号',
blogAbstract:'源地址'
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}excel实现鼠标用不同颜色十字定位表格_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
评价文档：
1页¥0.501页免费4页免费11页免费2页免费2页1下载券1页免费1页免费1页免费2页免费
喜欢此文档的还喜欢6页免费8页免费5页免费2页免费143页5下载券
excel实现鼠标用不同颜色十字定位表格|e​x​c​e​l​实​现​鼠​标​用​不​同​颜​色​十​字​定​位​表​格
把文档贴到Blog、BBS或个人站等：
普通尺寸(450*500pix)
较大尺寸(630*500pix)
你可能喜欢Appearance of table redone
in Excel 2000, I display 32,760 colors,
Excel shows only 56 colors at any time.& Following are the defaults.
Text within some cells can be viewed easier by selecting an area with
the mouse.&
[Color 0][Color 0]&
[Color 15]
[Color 15]&
[Color 30]
[Color 30]&
[Color 45]
[Color 45]
[Color 1][Color 1]&
[Color 16]
[Color 16]&
[Color 31]
[Color 31]&
[Color 46]
[Color 46]
[Color 2][Color 2]
&[Color 17]
[Color 17]&
[Color 32]
[Color 32]&
[Color 47]
[Color 47]
[Color 3]&
[Color 18]
[Color 18]&
[Color 33]
[Color 33]&
[Color 48]
[Color 48]
[Color 4]&
[Color 19]
[Color 19]&
[Color 34]
[Color 34]&
[Color 49]
[Color 49]
[Color 5]&
[Color 20]
[Color 20]&
[Color 35]
[Color 35]&
[Color 50]
[Color 50]
[Color 6]&
[Color 21]
[Color 21]&
[Color 36]
[Color 36]&
[Color 51]
[Color 51]
[Color 7]&
[Color 22]
[Color 22]&
[Color 37]
[Color 37]&
[Color 52]
[Color 52]
[Color 8]&
[Color 23]
[Color 23]&
[Color 38]
[Color 38]&
[Color 53]
[Color 53]
[Color 9]&
[Color 24]
[Color 24]&
[Color 39]
[Color 39]&
[Color 54]
[Color 54]
[Color 10]
[Color 10]&
[Color 25]
[Color 25]&
[Color 40]
[Color 40]&
[Color 55]
[Color 55]
[Color 11]
[Color 11]&
[Color 26]
[Color 26]&
[Color 41]
[Color 41]&
[Color 56]
[Color 56]
[Color 12]
[Color 12]&
[Color 27]
[Color 27]&
[Color 42]
[Color 42]&&
[Color 13]
[Color 13]&
[Color 28]
[Color 28]&
[Color 43]
[Color 43]&&
[Color 14]
[Color 14]&
[Color 29]
[Color 29]&
[Color 44]
[Color 44]&&
additional 16 colors below are not shown on the 40 color toolbar palette but can be seen under
Format, Cells, Pattern
Of the descriptive color names only those for index numbers 1 - 8 can be used in coding.
Olive Green
Dark Green
Dark Yellow
Light Orange
Light Blue
Bright Green
Light Yellow
Light Green
Light Turquoise
*to be updated* Excel 2000 names colors below (font not patterns) ******** *** ***** **
Periwinkle
Lite Turquoise
Dark Purple
Ocean Blue
Dark Blue+
Turquoise+
The default ColorIndex numbers can be found in HELP --&. Index --& ColorIndex Property
The colors names indicated on the
are for descriptive purposes only.& Excel
only recognizes names for Color 1 through 8 (Black, White, Red, Green, Blue, Yellow, Magenta, and Cyan).
Hex equivalents used in HTML
Additional 16 colors below are not shown on the 40 color toolbar palette but can be seen under Format, Cells, Pattern
Grayscale choices as seen in the Color Palette & (#grayscale)
&ABCDEFGHI
1PaletteSample
DecCalcFormula
00=(100-C2)*256/100
5151.2=(100-C3)*256/100
128128=(100-C4)*256/100
150153.6=(100-C5)*256/100
=(100-C6)*256/100
256256=(100-C7)*256/100
You can use the
to see the
RGB or Hex color values by using it to click here or on the color palette custom panel.
Click to view this page in
[IE only], hit F5 to restore.&
Read more about .
Modifications to Palette on a grayscale
Tools, Options, Color (tab),
Select a palette color to be modified,
modify (button)
on my Excel 2000 at this point
Standard tab has 17 preselected
white/ gray/ black choices at the bottom,
choose any of them and you get a
gray scale when you switch
to the custom tab you should see
the RGB values being
equal and will change as you
slide the choice selector.
Colors in Cell Formatting (#formatting)
The following colors may be used in formatting statements:& (see color table above)
& black, blue, green, cyan, red, magenta, yellow, and,
Color1, color2, color3, ..., color56
The parts of the format (unless changed) are:
Custom Formatting (#custom)
For more information on formatting see your Excel HELP and
page and particularly
[>=5]G [Red]-G [Blue]General
[>=5]G [Red]-G [Blue]General
[>=5]G [Red]-G [Blue]General
[>=5]G [Red]-G [Blue]General
[>=5]G [Red]-G [Blue]General
[>=5]G [Red]-G [Blue]General
[>=5]G [Red]-G [Blue]General
txt [>=5]G [Red]-G [Blue]General
was used to display Format
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 42]G [magenta]"Text:"@
[Blue][>=5]0.00; [Red][<=2]-0.00; [Color 45]G [magenta]"Text:"@
Format for ...
(each format is separated by a semicolon)
Positive Numbers(default); Negative Numbers(default); Zero(All other numbers); Text
Also see my
page (starting at #cellformat).& A generalized
related to the above Cell Formatting, and to ,
and .& Two more examples of cell formatting:
[Red][>=5]G[Color40][>=2]G[Color10]G[Color30]@
[Red][>0]"No";[Green]"Yes"
Conditional Formatting is covered on it's own
and can be used for
(like greenbar paper) [also ].
Pastel colors for 50% backgrounds HTML
Changing the Colors of your DOS session & (#DOS)
Color change is available at least in WinNT.&
Changing colors of your DOS window may or may not work for you.& I changed mine mainly in order to work with a specific package so that the wording is black on white.& This is easy to change but where there is no text the color will remain black.& ().&
Color can also be changed in the DOS window with the color command (Color&F0), which can be put into your Autoexec.bat -- to be effective you must reboot.& The screen can still turn black upon exiting an application but can be instantly reverted to white by typing Color.
The DOS assignments of the 16 colors (0-15) & &(#OE)
The normal VGA assignments do not apply to Excel, but they do apply to older VGA monitors used on IBM mainframes and DOS color assignments.
<td bgcolor="#
<td bgcolor="#
<td bgcolor="#
<td bgcolor="#
<td bgcolor="#
<td bgcolor="#
<td bgcolor="#
The assignments 0-15 are NOT those used by Excel ColorIndex
by Internet Explorer and most browsers in alphabetical order (#HTML):&
FUCHSIA,&&
white (white),&&
yellow&& & [Select area with mouse to read]
The above colors are supported in the HTML 3.2 standard but have not been
universally accepted by all browsers.& In HTML the colors are Fuchsia:
instead of M
#00FFFF instead of Cyan.
The colors
and as fonts below are the choices of colors for Outlook Express and
use the HTML names.& Colors 0-6, and 8 are very hard to see
the difference looking straight at the screen on what would now be an old laptop.
<Font Color="# Black,
<Font Color="# Navy,
<Font Color="# Green,
<Font Color="# Teal,
<Font Color="# Maroon,
<Font Color="# Purple
<Font Color="# Olive,
<Font Color="# Gray,
13 Fuschia,
14 Yellow,
<Font Color="# Black,
<Font Color="# Navy,
<Font Color="# Green,
<Font Color="# Teal,
<Font Color="# Maroon,
<Font Color="# Purple
<Font Color="# Olive,
<Font Color="# Gray,
13 Fuschia,
14 Yellow,
<Font Color="# Black,
<Font Color="# Navy,
<Font Color="# Green,
<Font Color="# Teal,
<Font Color="# Maroon,
<Font Color="# Purple
<Font Color="# Olive,
<Font Color="# Gray,
13 Fuschia,
14 Yellow,
<Font Color="# Black,
<Font Color="# Navy,
<Font Color="# Green,
<Font Color="# Teal,
<Font Color="# Maroon,
<Font Color="# Purple
<Font Color="# Olive,
<Font Color="# Gray,
13 Fuschia,
14 Yellow,
You should be able to distinguish link colors, if you can&t, consider specifying your own default colors (or even overrides).& Click
to establish visited links below.
Possible linkcolor changes&
link &&& #0000FF
visited #FF00FF&
link &&& #0000FF
visited #FF00FF&
Your ownlink colors&
You can change your own default colors for links:&
&& In Internet Explorer use Tools, Internet, General (tab), Fonts.&
& & & &(Not recommended but Accessibility button has option to override web page colors.)
&& In Firefox use Tools, Options, General (icon on left), Fonts and Colors, OK.& (color override on same dialog.)
To temporarily override a web pages visited links color you can use a
which would be
in effect until you reload the page or hit F5 (Reset/Reload).&
The bookmarklet
is particularly useful when viewing Google search results.& (Try it yourself, and Reset with F5.& If you like it
drag to links bar or a folder in your links bar, and do look at my
bookmarklets page.)
Since changing the actual colors is not possible, and would be ill advised, I&m not going to attempt to see if
it is possible to have eight distinguishable font colors for my laptop when
The Gamma can be changed for colors, and is somewhat equivalent from moving up or down
when viewing the laptop monitor, or by adjusting the tilt of the monitor.&
Most of problem distinguishing color pertains to fonts, backgrounds are okay and
bold text better than plain text.& To adjust the colors on the monitor use:&
Control Panel, Settings, Display (monitor), settings (tab), Advanced (button), Color (tab),
and change the color.& The gamma is seen as a color curve that you can distort for each
of the primary colors (Red, Green, Blue).& Other .
Help & Help & Help & Help & Help & Help &
Help & Help & Help & Help & Help & (#Help) &
HELP - related information
HELP --& Find --& color --& color --& "Basic number format codes"
HELP --& Index --& colorindex property --& colorindex property
Macros & Macros & Macros & Macros &
Macros & Macros & Macros & Macros & Macros & (#Macros) &
Setting Colors in Excel VBA Macros (#macros)
ActiveCell.Borders.Color = RGB(255, 0, 0)
ActiveCell.Borders(xlTop).Color = RGB(0, 0, 255)
ActiveCell.Borders(xlBottom).ColorIndex = 18
ActiveCell.Font.ColorIndex = 17
ActiveCell.Font.Color = RGB(255, 0, 0)
Selection.Interior.ColorIndex = xlNone
'xlColorIndexAutomatic or xlColorIndexNone
Selection.Interior.ColorIndex = 5
Selection.Interior.Color = RGB(200, 250, 200)
Selection.Interior.Color = &Hc8efac8
anyone still use octal
ActiveCell.Font.Background = {xlAutomatic | xlOpaque | x1Transparent}
Workbooks(&BOOK1.XLS&).Worksheets(&Sheet1&).Activate
ActiveWindow.GridlineColor = RGB(255,0,0)
Members of Excel Constants details:
Const xlColorIndexNone = -4142 (&HFFFFEFD2)
Const xlColorIndexAutomatic = -4105 (&HFFFFEFF7)
Const xlAutomatic = -4105 (&HFFFFEFF7)
Const xlBackgroundOpaque = 3
Const xlBackgroundTransparent = 2
Const xlOpaque = 3
Const xlTransparent = 2
Coloring Formulas Blue, and remove other font colors & (#ColorFormulas)
You could assign this to a
Sub ColorFormulas()
'xl97 up use xlcelltypeformulas
Cells.FONT.ColorIndex = xlAutomatic
Selection.SpecialCells(xlFormulas).FONT.ColorIndex = 5
as tested with SpecialCells.
Coloring Unprotected Cells Blue & (#FormatUnprotected)
Simulate a Lotus 1-2-3 feature to color unprotected cells blue ()
Sub FormatUnprotected()
For Each Item In Intersect(ActiveSheet.UsedRange, Selection.Cells)
If Item.Locked = False Then
Item.Font.colorindex = 32
Changing Font based on interior color and column (#chgfont)
Check for interior color of 41 (light blue).
Sub whiteONblue()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'pre XL97 xlManua
Dim cell As Range
'---Range(&A3:N100&).Select
For Each cell In Selection
If cell.Interior.colorindex = 41 And cell.Column = 4 Then
cell.Font.colorindex = 2 '2=white, 6=yellow
Application.Calculation = xlCalculationAutomatic
'pre XL97 xlManua
Application.ScreenUpdating = False
Clear Constants from Color Cells & (#ClearConstantsFromColorCells)
The purpose of ClearConstantsFromColorCells is clear out constants
from the cells that have an
interior color, and to retain the color of the cells.
Formulas, and empty cells will not be affected and aren't
even checked.
You can select an entire column without taking 6 seconds to
process every cell in that column because the cells processed
must also have constants.& Anything located
is by definition in the UsedRange.& Anything outside the UsedRange
could have color but won't have constants.
Have been doing so many change events lately that I turned
off events during the execution.& There is a little risk here
with EnableEvents turned off should the subroutine fail
some reason.
Sub ClearConstantsFromColorCells()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
On Error Resume Next
'In case no cells in selection
Application.EnableEvents = False
For Each Cell In Intersect(Selection, _
Cells.SpecialCells(xlConstants))
If Cell.Interior.ColorIndex &= 0 Then Cell.ClearContents
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Determining Interior Color of Another Cell & (#icolorF)
Function showRGB_wrong(rcell)
showRGB = rcell.Interior.Color)
End Function
You won't get too far in HTML using& range.Interior.Color &as above which
is supposed to show RGB but shows them in the wrong order and without leading zeros.& HTML has the
RGB components in the correct RGB order, so the following would
be what you need to equate to HTML color definitions.& Most people get the HTML wrong
because range.Interior.Color returns wrong order for RGB components.
reason the tables show the #prefix used in HTML colors to avoid all ambiguity.
Function showRGB(rcell)
Dim xColor As String
xColor = Right(&000000& & Hex(rcell.Interior.Color), 6)
ShowRGB = Right(xColor, 2) & Mid(xColor, 3, 2) _
& Left(xColor, 2)
End Function
Function showColorIndex(rcell)
showColorIndex = rcell.Interior.ColorIndex
End Function
Function ShowHTMLcolor(xcell) As String
Dim xColor As String
xColor = Right(&000000& & Hex(xcell.Interior.Color), 6)
ShowHTMLcolor = &#& & Right(xColor, 2) & Mid(xColor, 3, 2) _
& Left(xColor, 2)
End Function
Example: interior colorindex of B28
& & & =ShowColorIndex(B28)
key Ctrl+Alt+F9 forces a recalculation of *everything* in all open
workbooks whether or not Excel *thinks* recalculations are needed.& Changing
a format does not trigger cell recalculation, so you will have to force this
when you want the values to change.
The use of Volatile would also work but would probably have a severe
impact on your use of Excel.& the VBA equivalent of the shortcut is
& & Application.CalculateFull & & ' in Excel 2000
Coloring a selection based on a simple cell formula
(#colorofassignment)
in Excel.misc
also tested for formulas like:
=Sheet4'!A18
='Sheet four'!A18
=A2 =(D20)
in Excel.misc
Sub ColorOfAssignment()
Dim rng As Range, cell As Range
Set rng = Selection
'rng.Interior.ColorIndex = xlAutomatic
'clear color
For Each cell In Intersect(rng, _
rng.SpecialCells(xlFormulas))
On Error Resume Next
cell.Interior.ColorIndex = _
Range(Mid(cell.Formula, 2)).Interior.ColorIndex
On Error GoTo 0
Formatting a selection based on a simple cell formula
(#formatofassignment)
in Excel.misc
also tested for formulas like:
=A3 =Sheet4'!A18
='Sheet four'!A18
=A2 =(A20)
Sub FormatOfAssignment()
Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In Intersect(rng, _
rng.SpecialCells(xlFormulas))
On Error GoTo passby
Range(Mid(cell.Formula, 2)).Copy
cell.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
On Error GoTo 0
Populating cell value based on Cell Interior Color (#popvalue)
Sub Populate_color()
Dim cell As Range
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants, xlNumbers))
cell.Interior.ColorIndex = cell.Value
Setting Interior Color based on another Cell (#popbased)
Option Explicit
Global gblColorIndex As Long
Sub SetInteriorColor()
gblColorIndex = ActiveCell.Interior.ColorIndex
Sub PutInteriorColor()
Selection.Interior.ColorIndex = gblColorIndex
Sub SameInteriorAsA1()
Selection.Interior.ColorIndex = [A1].Interior.ColorIndex
Sorting on Interior Cell Color (#sorting)
This is a somewhat frequent request, that is going to be prone to errors
in interpretation of what color is.& You can obtain ColorIndex or
RGB but how would you sort that meaningfully.& Finally you are going
to have problems with recalculaton.&
This topic is covered further on a separate page:&
Interior Color, using Count, SUM, etc. (#count)
For Each cell In Selection
'Check for Black interior color
If cell.Interior.ColorIndex = 1 Then
- PhilCxn &&
- Patrick Molloy
If the colors you want to test are due to
then use the
same kind of test that you used for Conditional Formatting, and the results will be
immediate (no recalculation needed)
& =COUNTIF(D12:D16,TRUE)
& =SUMIF(D12:D16,TRUE,E12:E16)
If the colors are not from C.F.
you will have to use a User Defined Function to find
this information and since formatting is not
registered as a cell change you will have to wait for a recalculation
to occur to get a valid answer.&
You can but should not make the
macro Volatile, since by doing that you could bring your Excel to an
extremely slow state.& Examples follow in the next paragraph.
See my Functions for
described earlier for RGB, ColorIndex, and HEX.
Chip Pearson has some additional color functions using a little different approach unfortunately for whatever reason he does not provide (#cpcolorsx)
The following examples obtain the colorindex from another cell
which is best, because colorindex colors can be changed by
changing the palette.
Interior, colorindex of
& & =cellcolorindex(A$3,0)
if you installed in your personal.xls workbook to be available to all workbooks, use
& & =personal.xls!cellcolorindex(A$3,0)
Font, colorindex of
& & =cellcolorindex(A$3,1)
Count the cells with same interior color as A$3
& & =countbycolor(A$1:A$17,cellcolorindex(A$3,0))
Count the cells with same font color as A$3
& & =countbycolor(A$1:A$16,cellcolorindex(A$3,1),1)
Sum of the cells with same interior color as A$3
& & =sumbycolor(A$1:A$16,cellcolorindex(A$3))
#NAME? error will occur if you misspell one of the UDF (User Defined Function)
above or did not install the function.& A #VALUE! error may occur if you
did not install a function used inside or misuse it.& Instructions to install macros and User Defined Functions can be found on my
To work with shading instead of colorindex
use .pattern instead of .colorindex
and rename functions accordingly.& Specific
patterns include such name as:
xlvertical
, Bob Phillips, informative text, and macro subroutines
Determining the Row color based on cell value in that row (#rowcolor)
introduced in Excel 97 is limited to 3 conditions.&
With more than 3 conditions a macro would be required, such as shown below.&
kind of macro that you could use is an .& Note
even if you have an Event macro you will probably want a normal macro to fix things up
ahead of time.& A more complicated
differentiating text values, numbers, and empty cells
in addition to ranges of numbers.
ColorRowBasedOnCellValue()
'David McRitchie,
programming -- Color row based on value
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Intersect(Selection, ActiveCell.EntireColumn, _
ActiveSheet.UsedRange)
Select Case cell.Value
Case Is &= 50
cell.EntireRow.Interior.colorindex = 20
Case Is &= 40
cell.EntireRow.Interior.colorindex = 37
Case Is &= 20
cell.EntireRow.Interior.colorindex = 38
Case Is &= 0
cell.EntireRow.Interior.colorindex = 36
cell.EntireRow.Interior.colorindex = 44
End Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
Delete Rows Based on RED interior color in Column A (#DelRows)
The following will delete the entire row if it sees RED
as define by ColorIndex = 3
& &There are some caveats:
colors are invisible to VBA.
The ColorIndex = 3 & is the default someone could change it
Not everybody is going to be able to distinguish RED from
colors close to it and then there is Red/Green colorblindness.
Not all monitors are going to show colors alike, though RED
is pretty safe in that regard.
Comments for the following code can be found below the
on another web page.
Sub DeleteRowsRedInColA()
'David McRitchie
http://www.mvps.org/dmcritchie/excel/colors.htm
'Will not find color due to Conditional Formatting
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'pre XL97 xlManual
Dim rng As Range, ix As Long
Set rng = Intersect(Range(&A:A&), ActiveSheet.UsedRange)
For ix = rng.Count To 1 Step -1
If rng.Item(ix).Interior.ColorIndex = 3 Then
rng.Item(ix).EntireRow.Delete
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
HEX Conversions for RGB values (#hexconv)
Hex characters are actually characters, but represent binary
RGB values are represented by 6 hex digits.& The
first pair of digits represents Red, the next Green, and the last Blue.&
The values range from 0 to 255, or in hex from 00 to FF.&
Given a six hex digit representation in hex characters such
as 00C0C8 as hex characters simply use left, and mid to separate them the digit pairs.& Look in HELP for more information about HEX2DEC
and DEC2HEX.&
Suppose B14 had a Long (Binary) integer in it and you want 6 hex digits for RGB.&
HEX2DEC and DEC2HEX are part of the Statistical Analysis Toolpak
There are 256&sup3; RGB colors (16,777,216) and only 56 colorindex colors in the
so a one to one match of each is not only impossible, but the colors in
the palette can be reassigned to different colors.&
Conversion of Font color in Excel to a hex string for HTML
(via VBA code)& (#hexconvxl)
The following code was used in
conversion of
an Excel sheet to HTML.& Note Excel appears to store
values in reverse
order or perhaps this is just &big-endian& (main frames, 1234 order) vs. && (most PCs, 4321 order).
xColor = Right(&000000& & Hex(Selection.Cells(r, C).FONT.Color), 6)
xColor = &#& & Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)
If xColor && &#000000& Then _
x = &&font color=&&& & xColor & &&&&& & x & &&/font&&
Conversion of a single binary decimal number to decimal RGB components (WS formulas)
=RIGHT(&000000& & DEC2HEX(B14),6)
If you have a character value such as 00C0C8
or you start from a Long (Binary) integer, your can incorporate the above formula into the following
=HEX2DEC(Left(B15,2))
=HEX2DEC(MID(B15,3,2))
=HEX2DEC(MID(B15,5,2))
not provided for the possibility of 3-digit hex numbers in HTML like
which are equivalent to #333333 and
#660088, simply because I would never create the values
I also would never produce
RGB(red,green,blue)
strings for use in HTML and they are mostly
done incorrectly (without quotes) in most places that I see them used and harder to work with
visually when coding or comparing source.
Changing the Colors of your Excel Color Palette (#chgpalette)
To change a color in your palette go to
Tools --& Options --& Color
where you can change a color by double-clicking on a color cell.& Use Reset to revert back to defaults.& Also see &
Help --& colors, changing
Changing the default Shading Color (Fill Color) / (#fillcolor)
The default shading color is yellow (RGB: 255, 255, 0).& If you want
to change it for a workbook you will have to change the color in that position
of the palette.& Best to exchange the color with another color on
the palette say Lt Green (RGB: 204, 255, 204) using Tools, Options, Colors,
Modify, Custom.&
You can always use Reset to restore normal palette
for the workbook.&
A frequent question in the newsgroups is how to change the default colors in the
Cell Comments.& The name of the author is picked up from your
Tools, Options, General, User Name.& (you cannot change the color of the
red triangle)
Cell Comments are Tool Tips so to change your default you must change your
Windows default.& To change only once cell comment double-click on the border of the
cell comment and make your changes.&
To change Tool Tips.&
(Changes to Windows settings affect ALL applications)
& Windows START, settings, control panel, Display (monitor icon)
Retain a copy of your Original Control Display Settings (#BackupDisplay)
Before continuing it might be a good idea to name your current settings
and then name your new settings.
Press [Save As] button then assign the scheme to something
like Windows out of the box mmm dd, yyyyy
(current date).
Press [Save As] button then assign your own name to the scheme
i.e. &David
and all future changes would be made
to this scheme.
In the item: pull down, rather than pulling down you can simply place cursor in the box and then
use the cursor to cycle up or down through the choices.& Select &Tool Tip&
and make changes to font, fontsize, text color, background color as wanted.&
not sure if you actually will change the fontname or not.
You can select parts of the windows shown which will change the item selected,
but tool tips is not one of them, you have to use the pull down.
My own settings show: red text, yellow background, 8 point, MS Sans Serif,
non bold, non italic.& (I'm not sure what they were originally).
Also see .
More material on &
Changing the Colors of Worksheet Tabs (#tabs)
The color of the tabs is controlled by Windows,
you can change the scrollbar setting but it will affect everything in Windows and until Excel 2002 all tabs
had to be the same color as the scrollbar.&
Changes to size of scrollbar will change size of tabs.& Changes to color affect both tabs and
all scrollbars in Windows (or at least in Office).& You can change the fontsize on the sheet tab,
but not the font color or font size within
the sheet tabs.&
Before making changes see
on this page.
In Excel 2002 you can color individual worksheet tabs.&
Here is a tip from Jessica Kovalik in
at Microsoft &s Office site.
Select the sheets you want to color by holding down the CTRL key and clicking the tabs.
On the Format menu, point to Sheet, and then click Tab Color. You can also right-click the sheet tab and then click Tab Color.
Click the color you want, and click OK.
In VBA (for Excel 2002) the equivalent would be:
& & Activesheet.Tab.ColorIndex = 50
I believe the normal reason to color tabs is to provide an
organization to them.& You can sort sheet tabs with a macro.&
You can enhance your sorted arrangement by preceding the
sheet tab with some less conspicuous small letters prefixes.& i.e.
& & k.FunctKeys,
k.ShortCutKeys
If working with dates for sheetnames, spell the year out and place it first
& & 2002-10,
02-12, 2003-01
sort sheet tabs into alphabetical order in
The main topic on that page is to create a Table of Contents
with hyperlinks to the other sheets.&
Shorter versions
with just membernames can be found in .& A builtin alternative to navigate to
a sheet via the More Sheets dialog listing (also available from a ) is to
right-click on a scrolling arrow in lower left corner, the sheets are listed in the same order as the worksheet tabs at the bottom of your spreadsheet
(another reason to sort your worksheets).
Sort sheets by color of sheet tab, by
number, Chip Pearson's
There is one problem that I know of with the arrangement of sheet tabs.&
You will probably have trouble with
worksheet to be used in Mail Merge is not the first worksheet.
You can go through 90 worksheets very quickly using Ctrl+PageDn to
go down through the worksheet tabs, or Ctrl+PageUp to backup through the tabs.&
I prefer to use a couple of macros and
Color Triangles in Excel & (#triangles)
Red , upper-right corner of a cell indicates a &
Black, upper-left corner of a cell indicates highlight changes:&
Track changes,
upper-left corner of a cell indicates a potential error in the formula in the cell.& To turn off or adjust settings:&
select Tools, Options, and select the Error Checking (tab) and uncheck "Clear the Enable Background Error Checking".&
[ (dead link at MS]and you can change the color of error indicator triangle there as well.& Not shown if Track Changes is also in effect.
Purple, lower-right corner of a cell indicates a smart tag.
[] to turn off or adjust settings:&
select Tools, Options, and select the Error Checking tab.
Printing the comment indicator by aligning a shape over the upper right corner of cells with comments.& see
Manually Changing the Interior Color of Worksheet Cells (#manual)
Setting the interior color of the active cell, specifically
Applies to all cells in a selection that you can add to with the use
of the Ctrl key.
& & Format
--& cells --& patterns and colors
You can install a button on your toolbar to hasten the process,
it looks like a dripping paint can.
& & View --& customize --& toolbars --& custom --& format
select the dripping paint bucked, marked
Fill Color
and drag it to your toolbar (if not already there).
Color Coding Cells for Usage (#colorcoding)
You can color code cells or text to help with reading and/or to help
with data entry.& Please keep in mind that
people may not see colors the same as you do.
Pale color shading can be used to designate input areas, and can be expanded to different colors for input from different areas (departments).
Formula results might be shown with
a different text color, and the formulas themselves and other non-input areas such as descriptions might be protected from accidental changes.
Cells with links will generally show up in blue or purple with underlining.& Best not to change what people expect.
Highlight cells for review that you modified or find questionable.& Change them back at a later time.&
Also see tracking in .
Related: Conditional Formatting,
Format/Styles, Filtering,
Color Charts on the Web & (#colorcharts)
for Douglas R. Jacobson &s charts.
for a faster loading variation of Jacobson &s hex chart by Jack Wilson.
[/colors/]&
,& F.&David McRitchie,& All Rights Reserved