VBA ColorIndex Property of Excel VBA is very useful to set the fill colors, border colors and font colors. Excel VBA ColorIndex returns index values from 1 to 56, -4105 and -4142. You can set the default colors using VBA enumeration number -4105 ( or xlColorIndexAutomatic). We can set VBA colorIndex -4142 (or xlColorIndexNone) enumeration to clear the colors or set to no colors.
Syntax of Excel VBA ColorIndex
Here is the syntax of ColorIndex property of Excel VBA. You can set or return the color index value of the Excel Objects using the following VBA colorindex syntax.
expression.ColorIndex
Excel VBA Syntax to get the ColorIndex Value of the Excel Font, Interior or Border Color and store it in a Variable:
dblColorValue= expression.ColorIndex
Syntax to set the ColorIndex Value in Excel VBA to Excel Color Objects using Excel ColorIndex value:
expression.ColorIndex= IndexValue (1 to 56,-4105 or -4142)
ColorIndex in Excel VBA
Here are the list of Excel VBA ColorIndex Values and respective Colors:
ColorIndex | Excel VBA Color | ColorIndex | Excel VBA Color |
---|---|---|---|
1 | RGB(0,0,0) | 29 | RGB(128,0,128) |
2 | RGB(255,255,255) | 30 | RGB(128,0,0) |
3 | RGB(255,0,0) | 31 | RGB(0,128,128) |
4 | RGB(0,255,0) | 32 | RGB(0,0,255) |
5 | RGB(0,0,255) | 33 | RGB(0,204,255) |
6 | RGB(255,255,0) | 34 | RGB(204,255,255) |
7 | RGB(255,0,255) | 35 | RGB(204,255,204) |
8 | RGB(0,255,255) | 36 | RGB(255,255,153) |
9 | RGB(128,0,0) | 37 | RGB(153,204,255) |
10 | RGB(0,128,0) | 38 | RGB(255,153,204) |
11 | RGB(0,0,128) | 39 | RGB(204,153,255) |
12 | RGB(128,128,0) | 40 | RGB(255,204,153) |
13 | RGB(128,0,128) | 41 | RGB(51,102,255) |
14 | RGB(0,128,128) | 42 | RGB(51,204,204) |
15 | RGB(192,192,192) | 43 | RGB(153,204,0) |
16 | RGB(128,128,128) | 44 | RGB(255,204,0) |
17 | RGB(153,153,255) | 45 | RGB(255,153,0) |
18 | RGB(153,51,102) | 46 | RGB(255,102,0) |
19 | RGB(255,255,204) | 47 | RGB(102,102,153) |
20 | RGB(204,255,255) | 48 | RGB(150,150,150) |
21 | RGB(102,0,102) | 49 | RGB(0,51,102) |
22 | RGB(255,128,128) | 50 | RGB(51,153,102) |
23 | RGB(0,102,204) | 51 | RGB(0,51,0) |
24 | RGB(204,204,255) | 52 | RGB(51,51,0) |
25 | RGB(0,0,128) | 53 | RGB(153,51,0) |
26 | RGB(255,0,255) | 54 | RGB(153,51,102) |
27 | RGB(255,255,0) | 55 | RGB(51,51,153) |
28 | RGB(0,255,255) | 56 | RGB(51,51,51) |
VBA to Print ColorIndex Table
Here is the Excel VBA Macro to print Excel ColorIndex Values and respective colors in Excel Sheet.
Sub sbExcel_VBA_PrintColorIndex() rowCntr = 2 colCntr = 2 For iCntr = 1 To 56 Cells(rowCntr, colCntr).Interior.ColorIndex = iCntr Cells(rowCntr, colCntr) = iCntr If iCntr > 1 And iCntr Mod 14 = 0 Then colCntr = colCntr + 1 rowCntr = 2 Else rowCntr = rowCntr + 1 End If Next End Sub
Set ColorIndex in Excel VBA
Here are the list of Excel VBA code to set ColorIndex to a Range of cells in Microsoft Excel Sheet.
Font Colors in Excel VBA
We can set the font colors in Excel VBA using ColorIndex property of Font Object. Here is the simple excel vba font color macro to set the font color of a given range A1:E20.
Sub SetFontColorIndex_Range() Range("A1:E20").Font.ColorIndex = 40 End Sub
You can also get the fornt colors using ColorIndex and store it in a variable. Please check the below code snippet:
myVar=Range("A1").Font.ColorIndex
This will return the font color and assign to a variable.
Interior Colors in Excel VBA
We can change the Interior or fill colors of a range using Excel VBA ColorIndex Property. Excel Interior Color macro heps you to change the interior color of an obect.
Sub SetInteriorColorIndex_Range() Range("A1:E20").Interior.ColorIndex = 41 End Sub
You can get Cell colors using Excel VBA, here is the get cell color excel vba macro to get the cell background colors.
myVar=Range("A1:E20").Interior.ColorIndex
Border Colors in Excel VBA
ColorIndex property of Borders is very easy to set the border colors in Excel VBA. Here is
Sub SetBordersColorIndex_Range()
Range(“A1:E20”).Borders.ColorIndex = 42
End Sub
Clear Colors in Excel VBA
Some times we need to fill no colors in Excel, we can clear the Excel Object colors such as font, border and fill colors and set to automatic or no fill color. Here are example macro to clear the color and fill no colors.
Clear Background Color in Excel VBA
We often required to clear the backround or fill color of the excel object. We can use the following Excel Macro to clear the background colors and set no interior colors.
Sub SetClearBackgroundColor_ColorIndex_Range() Range("A1:E20").Interior.ColorIndex = -4142 End Sub
The above macro will set the Interior.ColorIndex to -4142 enumeration. Interior.ColorIndex = -4142 is enumeration to clear the backgound or fill color.
Similarly, we can clear the boder colors using Excel VBA as shown below:
Sub SetClearBorders_ColorIndex_Range() Range("A1:E20").Borders.ColorIndex = -4142 End Sub
We can set the font colors to default or automatic colors using Excel VBA ColorIndex property of Font object. Here is an example:
Sub SetClearFontColorIndex_Range() Range("A1:E20").Font.ColorIndex = -4105 End Sub
VBA Colors
We can set the colors in VBA using many approaches. We use ColorIndex Property, VBA Color Constants or set RGB Colors. We have already seen how to use ColorIndex in Excel VBA. Let us see the Excel VBA Color Constants and RGB Colors.
Excel VBA Color Constants
We can use the VBA Color Constants to set the colors of Excel Objects. Here is an easy to understand example:
Sub sbExcel_VBA_ColorConstants() Cells(2, 4).Interior.Color = vbBlack Cells(3, 4).Interior.Color = vbRed Cells(4, 4).Interior.Color = vbGreen Cells(5, 4).Interior.Color = vbYellow Cells(6, 4).Interior.Color = vbBlue Cells(7, 4).Interior.Color = vbMagenta Cells(8, 4).Interior.Color = vbCyan Cells(9, 4).Interior.Color = vbWhite End Sub
VBA Color Constant | VALUE | Excel VBA Color & RGB |
---|---|---|
vbBlack | 0x0 | RGB(0,0,0) |
vbRed | 0xFF | RGB(255,0,0) |
vbGreen | 0xFF00 | RGB(0,255,0) |
vbYellow | 0xFFFF | RGB(255,255,0) |
vbBlue | 0xFF0000 | RGB(0,0,255) |
vbMagenta | 0xFF00FF | RGB(255,0,255) |
vbCyan | 0xFFFF00 | RGB(0,255,255) |
vbWhite | 0xFFFFFF | RGB(255,255,255) |
RGB Colors in Excel VBA
We have only few color codes when we use Constants or ColorIndex Property. RGB helps us to use all posiisble combination of colors with Red,Green and Blue. Here is a simple Excel macro to explain the RGB in VBA.
Sub ChangeBackgourdColorRGB_Range() Range("A1:E20").Interior.Color = rgb(125, 205, 99) End Sub
rgb color can be any anumber between 0 and 255. Here are the list of RGB colors for Excel VBA ColorIndex color codes:
Excel VBA ColorIndex Related Examples
Excel VBA ColorIndex
VBA ColorIndex Property of Excel VBA is very useful to set the fill colors, border colors and font colors. Excel VBA ColorIndex returns index values from 1 to 56, -4105 and -4142. You can set
VBA Delete Columns based on Cell font Color – Excel Macro Example Code
VBA code to delete Columns based on Cell font Color example will help us to delete Columns based on cell font color from excel worksheet. We can use Delete method of Columns to delete the
VBA Delete Columns based on Cell Color – Excel Macro Example Code
VBA code to delete Columns based on Cell Color example will help us to delete Columns based on cell background fill color from excel worksheet. We can use Delete method of Columns to delete the
Delete rows based on Cell Color using VBA
VBA code to vba delete rows based on cell color example will help us to delete rows based on cell background fill color from excel worksheet. We can use Delete method of Rows to delete
Delete rows based on Cell font Color using VBA
VBA code to delete rows based on Cell font Color example will help us to delete rows based on cell font color from excel worksheet. We can use Delete method of Rows to delete the
Excel Chart VBA Examples and Tutorials
Here are the top most Excel Chart VBA Examples and Tutorials, show you how to deal with chart axis, chart titles, background colors,chart data source, chart types, series and many other chart objects.
Objects in Excel VBA – Workbook, Sheets, Range and Cell
Objects in Excel VBA. Tutorials to learn how to deal with different objects in Excel VBA - Workbook, Sheets, Range and Cell with Example codes to acsess it.
Excel VBA Object Model
Tutorials for understanding Excel VBA Object Model. Understanding the VBA Object Model is important to deal with different Excel Objects. Learn more here...
VBA Objects Properties and Methods in Excel
Most of the programming languages today are Object Based Or Object Oriented Programming Languages. Although Excel VBA is not a truly object oriented programming language, it does deal with objects.
In this topic:
- What are Objects?
- What are Properties?
- What are Methods?
- What is Object Browser?
Change Font Color in Excel VBA
Examples will show you how to change Font Color in Excel using VBA. While preparing the reports we change the font colors based on data Or a template.
Excel VBA Macros for Beginners – 15 Examples File download
Learning Basic Excel VBA By Examples is the easiest way to understand the basics of VBA to deal with Excel Objects, in this tutorial we will not covering any programming concepts, we will see how to access the different Excel Object using VBA.
Select Cell Range in Excel VBA
We select cell range in Excel VBA to do particular task with the selection. For example we may want to change the background color of a range and we may want to change the font to Bold. In this case we do not required to loop each and every cell of the range to change the background color and font.
Change Font to Bold in Excel VBA
Example to show you how to change font to Bold in Excel using VBA. Also find related examples like Font Color and Background Color changing in Excel VBA.
Change Background Color of Cell Range in Excel VBA
Description: It is an interesting feature in excel, we can change background color of Cell, Range in Excel VBA. Specially, while preparing reports or dashboards, we change the backgrounds to make it clean and get
Change the Color of Sheet Tabs in Excel VBA
It is good idea to change the color of sheet tabs while preparing dashboards in Excel. You can use Excel VBA to automate applying Colors to Worksheet Tabs.
A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.
LIMITED TIME OFFER
Advanced Project Planning Templates
Excel Templates
120+ Project Management Templates Pack
Excel | PowerPoint | Word
ULTIMATE RESOURCE MANAGEMENT TEMPLATE
Excel Template
50+ Essential Project Management Templates
Excel | PowerPoint | Word
Project Portfolio Management Templates
Excel | PowerPoint Templates
50+ Excel Project Management Templates
Excel Templates