Excel VBA ColorIndex - ANALYSISTABS.COM

Trang chủ » Visual Basic Color Codes » Excel VBA ColorIndex - ANALYSISTABS.COM

Có thể bạn quan tâm

Skip to content Toggle Navigation

120+ Professional Project Management Templates!

Save Up to 85% LIMITED TIME OFFER

View DetailsAnalysistabs Search for: Go to... Home » Excel VBA » Excel VBA ColorIndexPreviousNextExcel VBAExcel VBA ColorIndexColorIndex|

Share This Post

VBA ColorIndex In This Article
  • Syntax of Excel VBA ColorIndex
  • ColorIndex in Excel VBA
    • VBA to Print ColorIndex Table
  • Set ColorIndex in Excel VBA
    • Font Colors in Excel VBA
    • Interior Colors in Excel VBA
    • Border Colors in Excel VBA
  • Clear Colors in Excel VBA
    • Clear Background Color in Excel VBA
  • VBA Colors
    • Excel VBA Color Constants
    • RGB Colors in Excel VBA
VBA Code Library
  • VBA Code Explorer
  • Application
    • Methods
      • Calculate
      • CalculateFull
      • FindFile
      • Goto
      • Run
      • Wait
    • Properties
      • Calculation
      • Caption
      • DisplayAlerts
      • DisplayFormulaBar
      • DisplayFullScreen
      • DisplayScrollBars
      • DisplayStatusBar
      • EnableEvents
      • Path
      • ScreenUpdating
      • StatusBar
      • UserName
      • WindowState
  • Workbook
    • Methods
      • Activate
      • Close
      • Protect
      • ProtectSharing
      • RefreshAll
      • RejectAllChanges
      • RemoveUser
      • RunAutoMacros
      • Save
      • SaveAs
      • SaveCopyAs
      • SendMail
      • Unprotect
      • UnProtectSharing
  • Worksheet
    • Methods
      • Activate
      • Calculate
      • Copy
      • Delete
      • Move
      • Protect
      • Select
      • Unprotect
  • Excel VBA Range
    • Methods
      • Activate
      • AddComment
      • AdvancedFilter
      • Calculate
      • Clear
      • ClearComments
      • ClearContents
      • ClearFormats
      • ClearHyperlinks
      • Copy
      • CopyFromRecordset
      • Cut
      • Delete
      • Excel VBA PasteSpecial
      • Find
      • Insert
      • ListNames
      • Merge
  • ActiveX Controls
    • CheckBox
      • Properties
        • Alignment
        • BackColor
        • Caption
        • Enabled
        • Height
        • Left
        • TextAlign
        • Top
        • Width
    • ComboBox
    • CommandButton
    • Image
    • Label
    • ListBox
      • Properties
        • BackColor
        • BorderStyle
        • ColumnCount
        • Enabled
        • Height
        • Left
        • MultiSelect
        • TextAlign
        • Top
        • Width
    • OptionButton
      • Caption
    • TextBox
  • Userform
    • CheckBox
    • ComboBox
    • CommandButton
    • Image
    • Label
    • ListBox
    • OptionButton
    • TextBox
    • Tutorial & Examples
  • Projects
    • Calculator using Excel VBA UserForm
    • Data Entry Userform
    • Excel VBA UserForm: Difference Between Two Dates
    • TOC creator in Excel Workbook using VBA
    • VBA List Folders Subfolders in Directory
    • VBA to Append Data from multiple Excel Worksheets into a Single Sheet – By Column
    • VBA to Consolidate data from multiple Excel Worksheets into a Single Sheet – By Row

REAL-TIME

VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

View Details

120+ PROFESSIONAL

Project Management Templates

View Details
120+ PM Templates Includes:

  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

Share Post

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.

Excel VBA ColorIndex

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. VBA to Print ColorIndex in Excel Range

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 background 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 background 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 possible 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 number between 0 and 255. Here are the list of RGB colors for Excel VBA ColorIndex color codes: Excel VBA ColorIndex with RGB

Effortlessly Manage Your Projects and Resources120+ Professional Project Management Templates!

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.

Save Up to 85% LIMITED TIME OFFER
Browse All TemplatesProject Management Templates

All-in-One Pack 120+ Project Management Templates

120+ PM Templates

Essential Pack 50+ PM Templates

50+ PM Templates

Excel Pack 50+ Excel PM Templates

50+ Excel Templates

PowerPoint Pack 50+ Excel PM Templates

50+ PPT Templates

MS Word Pack 25+ Word PM Templates

25+ Word Templates
Ultimate Project Management Template
Ultimate PM Template
Ultimate Resource Management Template
Resource Management
Project Portfolio Management Templates
PPM Templates

Related Posts

VBA ActiveSheet – Excel Active Sheet Object VBA ActiveSheet – Excel Active Sheet Object Gallery

VBA ActiveSheet – Excel Active Sheet Object

August 6, 2019|0 Comments Excel VBA Copy Range to Another Sheet with Formatting Excel VBA Copy Range to Another Sheet with Formatting Gallery

Excel VBA Copy Range to Another Sheet with Formatting

July 5, 2019|1 Comment Show or Hide|Unload a userform Show or Hide|Unload a userform Gallery

Show or Hide|Unload a userform

October 2, 2015|1 CommentCategories: Excel VBATags: ColorIndexLast Updated: December 27, 2022

Leave A Comment Cancel reply

Comment

Save my name, email, and website in this browser for the next time I comment.

Effectively Manage Your Projects and  Resources

With Our Professional and Premium Project Management Templates!View Details

ANALYSISTABS.COM provides free and premium project management tools, templates and dashboards for effectively managing the projects and analyzing the data.

We’re a crew of professionals expertise in Excel VBA, Business Analysis, Project Management. We’re Sharing our map to Project success with innovative tools, templates, tutorials and tips.

Project Management

Excel VBA

Download Free Excel 2007, 2010, 2013, 2016, 2019 + Microsoft Office 365 Templates, Dashboards, Tools for Project Management, Data Mining, Analysis, Visualization and VBA Automation. Learn Project Management and VBA for Microsoft Excel, Word, PowerPoint, Access, Outlook to develop applications for retail, insurance, banking, finance, telecom, healthcare domains.

Analysistabs Logo

© 2023 Analysistabs | Sitemap | Your Privacy | Terms

Page load link Go to Top

Từ khóa » Visual Basic Color Codes