EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Tips VBA Color Index
Secondary Sidebar
VBA Tips
  • VBA Tips
    • VBA RGB
    • VBA Web Scraping
    • VBA AutoFill
    • VBA GoTo
    • VBA Color Index
    • VBA Wait
    • VBA Paste
    • VBA Copy Paste
    • VBA Remove Duplicates
    • VBA Sleep
    • VBA Font Color
    • VBA PowerPoint
    • VBA Borders
    • VBA Pivot Table
    • VBA Save As
    • VBA Tutorial For Beginners
    • VBA Charts
    • VBA Dictionary
    • VBA Conditional Formatting
    • VBA Paste Values
    • VBA Pause
    • VBA Refresh Pivot Table
    • VBA Macros
    • VBA Examples
    • Programming in Excel
    • VBA SendKeys
    • VBA Save Workbook
    • VBA PasteSpecial
    • VBA Function in Excel
    • VBA Visual Basic Applications
    • VBA Return

VBA Color Index

By Ashwani JaiswalAshwani Jaiswal

VBA Colour Index

Excel VBA Color Index

VBA Color Index is a function by which we can change the color of the cell or text which is located in the Home menu under the Font section. Same can be done through VBA Macros. We can change the color of the text or background color of any cell by VBA Macro.

Examples of Excel VBA Color Index

Following are the different examples:

Example #1

It is very easy to color any cell or text with the help of VBA Coding.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,522 ratings)
You can download this VBA Color Index Excel Template here – VBA Color Index Excel Template

For this, we have considered a text “Color” in a cell as shown below.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

VBA Colour Index Example 1-1

Now to color the selected cell of any choice through VBA coding, press Alt + F11. It will take us to the VBA window. Now insert a new Module. For this go to the Insert menu and click on Module option as shown below.

Insert Module

After that, we will get a blank module, where we will write the code for Color Index. For this, frame the body for code with Subcategory. For better understanding name the Subcategory with the function of use.

Code:

Sub Color()

End Sub

VBA Colour Index Example 1-2

First, select the range and choose the cell which needs to be colored. Here we have cell B2 with text “Color”.

Code:

Sub Color()

Range("B2")

End Sub

VBA Colour Index Example 1-3

Now type and select Interior as shown below. This has many properties to select.

Code:

Sub Color()

Range("B2").Interior

End Sub

VBA Colour Index Example 1-4

After that type and select Color from the list. This will enable different colors to work on.

Code:

Sub Color()

Range("B2").Interior.Color

End Sub

VBA Colour Index Example 1-5

In VBA there is only one way to select any color. Type “vb” before the name of the color. We can add and mix only 8 main base colors. Which are Red, Black, Green, Yellow, Blue, Magenta, Cyan and White. Type vb at the start of these colors as shown below.

VBA Colour Index Example 1-6

To complete the type any color as per above format. Let’s consider vbGreen.

Code:

Sub Color()

Range("B2").Interior.Color = vbGreen

End Sub

VBA Colour Index Example 1-7

Now run the code using F5 key or manually. We will see the color of the selected cell in the range is changed to Green color as shown below.

VBA Colour Index Example 1-8

Example #2

We can change the color of any selected cell in the combined mixture of Red-Green-Blue (or commonly known as RGB). We can decide the color shade and amount of shade we want to give. More value given to any color will make it of dark shade.

For this, we will consider the same cell named “Color”. Now go to VBA window and open a new module. And in the new module write the Subcategory of Color as shown below.

Code:

Sub Color()

End Sub

VBA Colour Index Example 2-1

Select the range or cell with the command Range and select cell “B2”.

Code:

Sub Color()

Range("B2")

End Sub

VBA Colour Index Example 2-2

Now as shown in example-1 select Interior.Color which will enable all the applicable colors to that selected cell and as explained RGB give any amount of color shade range. Here we have given Red – 200, Green – 100 and Blue – 150.

Code:

Sub Color()

    Range("B2").Interior.Color = RGB(200, 100, 150)

End Sub

VBA Colour Index Example 2-3

Once done, run the code. As we can see in below screenshot, the background color of cell B2 is changed from white to purple shade. By this, we can create as many colors as required.

 Example 2-4

Example #3

We have seen the process of changing the background color of any cell. Now we will how to change the cell content or font color. For this also we will consider the same text as seen in example-1.

VBA Colour Index Example 3-1

Now go to VBA and open a new module. Once done, start writing the subcategory as shown below.

Code:

Sub Color()

End Sub

VBA Colour Index Example 3-2

First select the range and choose the cell whose text needs to be colored. Here we have cell B2 with text “Color”.

Code:

Sub Color()

Range("B2")

End Sub

VBA Colour Index Example 3-3

Now as seen in example-2, instead of Interior.Color, select Font.Color function as shown below. Font command will select the font and text selected cell.

 Example 3-4

And now use RGB to create the required color and implement it to selected cell text. Here we have given color range Red – 153, Green – 50  and Blue – 204.

Code:

Sub Color()

    Range("B2").Font.Color = RGB(153,  50,  204)

End Sub

 Example 3-5

Once done, run the code using the F5 key or manually.

 Example 3-6

We will see, the color of the text in cell B2 will now be changed to Purple color shade.

Example #4

Microsoft Excel has defined every possible color and given the color code as well. In the above examples, we used Interior command to change the color of any cell. And with the help of RGB, we selected the color shades as well. Now we will select the specific color code and change the background color of the selected cell with the help of Color.Index. Defined color indexes are below.

Color Code

These Color Indexes are taken and available from the Microsoft website. Now to go to the VBA window and open a new module. And in a new module write the Subcategory.

Code:

Sub Color()

End Sub

 Example 4-1

First, select the range and choose the cell whose font needs to be colored. Here we have cell B2 with text “Color”.

Code:

Sub Color()

Range("B2")

End Sub

 Example 4-2

After writing Font. This will enable the fonts to excel.

 Example 4-3

And just after continuing the coding, write and select Color.Index and select any of the color code as shown above. Here we are selecting color index 10 which represents green.

Code:

Sub Color()

    Range("B2").Font.ColorIndex = 10

End Sub

 Example 4-4

Now run the code using F5 key or manually. Then we will see the font color of selected cell B2 is now changed to Green as shown below.

 Example 4-5

We can do this for Background color as well.

Pros of VBA Color Index

  • It is as easy changing the color from VBA as changing the same from excel functions.
  • Any color can be selected.

Things to Remember

  • It is always recommended to use VBA Color Index when statistical data are used.
  • Always save the file in Macro-Enabled Excel so that created code and macro can be used multiple times.
  • Using color index beyond the list of 56 Colors is not possible.
  • Color Index can be used for both Font and Background color change.

Recommended Articles

This has been a guide to Excel VBAColor Index. Here we discussed how to use Color Index in VBA along with some practical examples and downloadable excel template. You can also go through our other suggested articles–

  1. VBA Arrays
  2. VBA Number Format
  3. VBA Find
  4. VBA Do While Loop
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more