From index 1 to 56 are actual colors which provides various types of colors. Have a look at below color variations
Let us now see how we can implement ColorIndex in VBA codes:
Sub ColorIndexExample1()
Sheet1.Range("C2").Interior.ColorIndex = 1 'Black
Sheet1.Range("C3").Interior.ColorIndex = 3 'Red
Sheet1.Range("C4").Interior.ColorIndex = 5 'Blue
End Sub
Here we have set the color of the cell using ColorIndex. ColorIndex = 1 is used to change the cell color to Black. Similarly, ColorIndex 2 and 3 are used to change the cell color to Red and Blue respectively. ”Example 2: Changing Border Color”
Code:
Sub ColorIndexExample2()
Sheet1.Range("C2").Borders.ColorIndex = 3 'Red
Sheet1.Range("C3").Borders.ColorIndex = 4 'Green
Sheet1.Range("C4").Borders.ColorIndex = 6 'Yellow
End Sub
Explanation:
In the above code, we have changed the border color of the cells using ColorIndex property available under Borders function of Range. ”Example 3: Changing Font Color”
Code:
Sub ColorIndexExample3()
Sheet1.Range("B2").Font.ColorIndex = 1 'Black
Sheet1.Range("B3").Font.ColorIndex = 3 'Red
Sheet1.Range("B4").Font.ColorIndex = 5 'Blue
End Sub
Explanation:
In this third example, we have changed font color of the cell using ColorIndex property.
Both -4105 and -4142 are special enumeration to set the ColorIndex to Automatic or None. If ColorIndex is supplied as -4105 (xlColorIndexAutomatic) then cell color, border or font is set to Automatic. Below is a sample code
Code:
Sub ColorIndexExample4()
Sheet1.Range("C2").Interior.ColorIndex = xlColorIndexAutomatic
End Sub
Explanation:
The above code changes the cell color of C2 on Sheet1 to Automatic which is white here. Similarly, -4142 (xlColorIndexNone) is used to set ColorIndex to none. Below is a sample code of the same:
Code:
Sub ColorIndexExample5()
Sheet1.Range("C3").Interior.ColorIndex = xlColorIndexNone
End Sub
Explanation:
The above code changes the cell color of C3 on Sheet1 to None.=”How to Get ColorIndex of a Cell?” Below is sample code to get the ColorIndex value of a Cell Color, Font or Border:
Code:
Sub ColorIndexExample6()
Sheet1.Range("C2").Value = Sheet1.Range("B2").Interior.ColorIndex 'Cell ColorIndex
Sheet1.Range("C3").Value = Sheet1.Range("B3").Font.ColorIndex 'Font ColorIndex
Sheet1.Range("C4").Value = Sheet1.Range("B4").Borders.ColorIndex 'Border ColorIndex
End Sub
Explanation:
In the above code, we are reading the ColorIndex of Cell, Font, and Border Colors the value of the ColorIndex is then stored back in the sheet in the column. You can also make use of few color Constants defined in Excel VBA for quick reference.
Code:
Sub ColorConstantsExample()
Sheet1.Range("C2").Interior.Color = vbBlack
Sheet1.Range("C3").Interior.Color = vbBlue
Sheet1.Range("C4").Interior.Color = vbCyan
Sheet1.Range("C5").Interior.Color = vbGreen
Sheet1.Range("C6").Interior.Color = vbMagenta
Sheet1.Range("C7").Interior.Color = vbRed
Sheet1.Range("C8").Interior.Color = vbWhite
Sheet1.Range("C9").Interior.Color = vbYellow
End Sub
There is also one more way to work with colors using RGB (Red Green Blue) function. The function requires 3 numbers from 0 to 255 [e.g. RGB(23,122,98)]. Have a look at below code:
Sub RGBExample()
Sheet1.Range("B2").Interior.Color = RGB(255, 0, 0) 'Red
Sheet1.Range("B3").Interior.Color = RGB(0, 255, 0) 'Green
Sheet1.Range("B4").Interior.Color = RGB(0, 0, 255) 'Blue
End Sub
Explanation:
In the above code, we are using RGB VBA function to change the cell colors.
Have a look at How to get RGB Codes of a Color and VBA Code to Change Cell Color post to know more about this.
This Excel VBA Code helps to Get User Name. Here is an example environ(username) or Application.username.This macro gets the username from active directory.
VBA Code to Filter Data in Excel Here is an easy reference code which filters data in the sheet. In the code, we have filtered the data in three steps. Step 1: Remove existing filter from…
QR Code Generator Tool in Excel Hi Friends, today we are going to learn something which is more closure to many businesses. As the businesses going global and digital, there is a need to adopt…
This tutorial shows you three easy ways to add hyperlinks in Excel. You will learn how to insert, change, and remove hyperlinks in your worksheets. It also explains how to fix links that don’t work.
Hyperlinks are often used on the internet to move between websites. In Excel, you can create links like that too. You can make a link to another cell, a different sheet, or even another workbook. You can also link to open a new Excel file or start an email message. This guide will show you how to do all of this in Excel 2016, 2013, 2010, and older versions.
VBA Code to Convert MM.DD.YYYY To DD.MMM.YYYY in Excel In different parts of the world, there are different languages spoken and written. With this, a VBA programmer also faces language related issues while writing a…
File Manager tool is an Excel based tool which helps you to delete or move unwanted files from your system. It requires a source and destination folder (in case you want to move files). First it lists all the files available in the folder or sub-folders then you can select the action to be taken for each file such as Move or Delete. With a click of button, tool will take all necessary actions.