Excel VBA, Cell Fill Color
In this article I will explain how you can set and get the fill color of cells using VBA. There are many times you may need to set the background colors of certain cells based upon a condition. Using conditional formatting is not always feasible as the formula may get very complex.
There are multiple ways to specify the color that you wish to use. Let us look at some examples
For more information about using colors in VBA for excel please see VBA Excel Colors.
The code below changes the fill color of cell “A1” to red:
'Using XlRgbColor Enumeration
Range("B2").Interior.Color = rgbDarkGreen
'Using Color Constants
Range("B3").Interior.Color = vbGreen
'Specifying the RGB values
Range("B4").Interior.Color = RGB(255, 0, 0)
'Using Color Code
Range("B5").Interior.Color = 15773696
'Using Color Index
Range("B6").Interior.ColorIndex = 6
'You can also specify no color or automatic color using
Range("B7").Interior.ColorIndex = xlNone
Range("B8").Interior.ColorIndex = xlColorIndexAutomatic
This is how the Excel will look before and after running the code. Before:
For more information about color constants please see Excel VBA ColorConstants.
For a complete example on setting the background color of cells based on color codes please see Excel VBA Color Code.
The line below gets the color code of the color used to fill cell A1 and prints it in cell B1:
Cells(1, 2) = Range("A1").Interior.Color
The following line of code does the same:
Cells(1, 2) = Range(Cells(1, 1), Cells(1, 1)).Interior.Color
For an example on getting the fill color used in a cell or range please see Excel VBA, Get Color Code.
Let us look at a more practical example of changing the background color of cells. Say, in your Excel program, you have an option to change the color theme of a sheet (or part of a sheet). So, you have 2 color themes, black and blue and a button for each. This is how it looks before:
You will use the code below to – check the background color of each cell in the table. If it is black, change it to blue and vice-versa.
Private Sub SetBlack_Click() Dim forecastTable As Range Set forecastTable = Range("B7:K17") For Each cell In forecastable 'Check the color of the cell If cell.Interior.color = 9851952 Then 'If it is blue, change it to black cell.Interior.color = 0 End If Next cell End Sub Private Sub SetBlue_Click() Dim forecastTable As Range Set forecastTable = Range("B7:K17") For Each cell In forecastTable 'Check the color of the cell If cell.Interior.color = 0 Then 'If it is black, change it to blue cell.Interior.color = 9851952 End If Next cell End Sub
This is how the Excel will look after clicking on Blue Theme:
Instead of checking the color of each cell, you can also directly specify the range for which you want to change the color. This example was used to demonstrate how to change the color of all cells from one value to another
- Excel VBA, Get Color Code
- Excel VBA Color Code
- VBA Excel Colors
- Excel VBA, Formatting Cells and Ranges using the Macro Recorder
- Excel VBA, Fill Pattern
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com
One thought on “Excel VBA, Cell Fill Color”