Excel VBA – Get RGB Color of Cell – Change Background

VBA to get Excel Cell Background Color RGB Code & Change it

Hope you would like these in-depth Excel cell color formatting techniques.

In Excel macro to get cell color in RGB code or change it, we can follow any of these methods.

1. Excel VBA get RGB Color of Cell or Convert Cell Color to RGB Code
2. VBA: Excel Color index to change cell color
3. Manual: Excel Background Color of Cell

This page is all about Excel Cell formatting.

Let’s see with an example on how to play with this RGB code.

1. Excel VBA Get RGB Color of Cell

To do this, first get the color code of a cell using its Cells.Interior property.

Excel will return a hexadecimal value. Which can be to its corresponding RGB value using the formula in the vba code.

Once we are familiar with this code, then it will be easy to

• Get the Excel color index of a cell.
• Change cell Interior Color in VBA.
• Change cell color based on value in another cell.
• Count number of cells based on its color.

Sub Excel_VBA_Get_RGB_Color_Of_Cell()
    Dim cColor, cRed, cGreen, cBlue
    
    'Get Cell background color VBA Value. Convert this Color Value to RGB Value
    cColor = ActiveSheet.Cells(1, 1).Interior.Color
    
    'Convert Color Code to RGB
    cRed = (cColor Mod 256)
    cGreen = (cColor \ 256) Mod 256
    cBlue = (cColor \ 65536) Mod 256
    MsgBox cColor & "; " & VBA.RGB(cRed, cGreen, cBlue)
End Sub

Many times, we have to convert this hexadecimal to RGB code to use it in with many build in function calls. So, this Excel VBA get RGB color of cell will be much useful in many ways.

Above VBA code converts the cell color to its corresponding RGB code. The below code does the reverse.

2. Excel VBA Change Cell Color Index using RGB code

Using this Excel VBA, change cell background color.

Also if you need to get Cell background color property in a VBA variable field to use it inside a IF loop. The code below serves both the purpose.

Sub Excel_VBA_Change_Cell_Color_RGB()
    'Set Red, Green, Blue Color Coding levels
    R = 50
    G = 150
    B = 255

    'Get the Corresponding RGB Code
    Dim RGBCode As Long
    RGBCode = VBA.RGB(R, G, B)

    'Excel VBA Change Cell Color Macro Code
    Sheets(1).Cells(1, 1).Interior.Color = RGBCode
    'If a Cell color has to be changed based on another cell value, 
    'then embed the above code inside a IF condition

    'Cell Interior Color VBA
    'Cell background Color in VBA
    Sheets(1).Cells(1, 1) = Sheets(1).Cells(1, 1).Interior.Color

    'Additional Formating info
    'Similar to Background color, fond color also can be changed with below code
    'Sheets(1).Cells(1, 1).Font.Color = RGBCode

End Sub

This code can also be used to change the background color of a cell based on a cell value. It can be used as an alternative for conditional formatting.

More Tips: Looping Structure and Conditional Statements in VBA

3. Change Excel Cell Color Background or Font Color

To do this manually, we have to first select cell & change Excel color index as below

1. Select ‘Home’ Menu.
2. Click ‘Fill Color’ in ‘Font’ tab.
3. Choose a color from the drop down or
4. Right click on cell & choose Format Cells option to perform all the formatting changes

This will change the cell background color or range selected by user. But there are times, where we have to change the background color or font color based on condition within a VBA code. This can be done by using the Interior.Color property of Excel cell discussed above.

With this property the Excel Color Index, Format and Theme of an worksheet cell can be modified, during the VBA Macro runtime.

Related: Different Methods to get or update value in Excel Worksheet

4. Open VBA Color Palette

This is another method to get user input using the built in color palette in Excel.

Sub Open_Color_Palette_Set_Color()
    Dim ret As Boolean

    'Select a Cell before invoking this command.
    ThisWorkbook.Sheets(1).Cells(1, 1).Select
    'Application.Dialogs.Item(xlDialogColorPalette).Show
    'Application.CommandBars("Fill Color").Visible = True
    Application.Dialogs(xlDialogPatterns).Show
    
End Sub

This command has to select a cell within the code before opening color palette. Once executed, this code will open the color palette allowing user to choose a color. Once the color is chosen, the selected cell background will be applied with that color.

Excel VBA Cell Background Color & Formatting

We saw mainly on how to get RGB code for a cell color & change the cell background color using VBA . Once if we know how to get or set a color code through VBA, it will be easy to manipulate them inside our coding.

In addition to font and background color, we can also do the below list of cell formatting operations.

1. Wrap Text.
   • Sheets(1).Cells(1, 1).WrapText = True
2. Alignment
   • Sheets(1).Cells(1, 1).HorizontalAlignment = xlCenter
3. Font Change
   • Sheets(1).Cells(1, 1).Font.Name = “Arial”
4. Font Size
   • Sheets(1).Cells(1, 1).Font.Size = 12
5. Autofit
   • Columns(“A:A”).EntireColumn.AutoFit
6. Font Bold or Italic
   • Sheets(1).Cells(1, 1).Font.Italic = True

There are plenty of such option available to change the format of a cell. We can try to record macro while manually doing formatting changes and get the corresponding code to perform these Formatting.

Additional Reference on Cell Color & Interior Property

• http://office.microsoft.com/en-in/excel-help/change-cell-background-color-using-vba-HA001136627.aspx
• http://stackoverflow.com/questions/365125/how-do-i-set-the-background-color-of-excel-cells-using-vba
• http://msdn.microsoft.com/en-us/library/office/ff837616(v=office.15).aspx