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.
We are currently offering our Excel VBA Course at discounted prices. This course includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Here is another best rated Excel Charts and Graph Course from excelsirji. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? Here is a simple code which can help you.
Free File Renamer Tool – Quickly Rename files batch using Excel VBA Here is another help code and tool for programmers to rename files. You can use this tool for renaming all files available in…
In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location
In this article we are going to show you how you can delete file or files using a single line of VBA code. 1. Delete a specific file from the folder, 2. Delete specific type of files from the folder, 3. Delete all files from the folder
Through formatting the cells, you can make your Excel data more beautiful and easier to understand. If you record a macro to add borders to Excel range, you will notice that it generates 30 plus lines of code for just a small work. Here we are sharing one line of code which does the same thing.
To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.