Last Updated on February 7, 2022 – 2 min read
To make your Excel report more attractive and design, you need to use colors. How do you use RGB color in VBA? In VBA, there are two main methods for coloring objects cells, range, sheets, borders.
In this post, you will discover how to use the color methods as ColorIndex and the RGB (Red-Green-Blue) code method.
ColorIndex Method
The ColorIndex method starts at 1 to 56.
Below is the macro procedure to display the table of 56 colors.
Sub See_ColorIndex()
'Declaration
iRow = 1 ' define the row
jCol = 1 ' define the column
'Loop on the 56 ColorIndex values to diplay
For jCol = 1 To 56
Cells(iRow, jCol).Value2 = Col 'Print the number of the color
Cells(iRow, jCol).Interior.ColorIndex = Col ' Print the 56 color
jCol = jCol + 1
'Line break
If jCol = 10 Or jCol = 20 Or jCol = 30 Or jCol = 40 Or jCol = 50 Then
iRow = iRow + 1
jCol = 1
End If
Next Col
End Sub
RGB Color Method
RGB means Red-Green-Blue. Values vary from 0 to 255 for each color.
Below are a few examples of colors:
| Red | Green | Blue | Result Color |
|---|---|---|---|
| 0 | 0 | 0 | Black |
| 255 | 255 | 255 | White |
| 255 | 0 | 0 | Red |
| 0 | 255 | 0 | Green |
| 0 | 0 | 255 | Blue |
| 255 | 255 | 0 | Yellow |
| 255 | 0 | 255 | Magenta |
| 0 | 255 | 255 | Cyan |
The RGB color allows you to find variations of all colors.
Sub RGB_Color()
'The Color method with RGB
iRow = 10
jCol = 10
Cells(iRow, jCol).Interior.Color = RGB(190, 49, 30)
End Sub
Get the R-G-B values
You can recover the RGB decomposition of colors in a cell by finding the numerical value of red, blue, and green separately. Each value is between 0 and 255.
Sub Get_RGB_Details()
Dim ColorToGet, NewColor As Double
'Get the color of a cell
ColorToGet = Sheets(1).Cells(2, 5).Interior.Color
'Get the decomposition of color
Red = ColorToGet And 255
Green = ColorToGet \ 256 And 255
Blue = ColorToGet \ 256 ^ 2 And 255
'Set the color on a new variable
NewColor = RGB(Red, Green, Blue)
End Sub
Blinking cell in VBA
To make a cell flash, call the function below recursively inside itself.
You can use the OnTime method of the application object. It sets a delay of your code. The OnTime method executes the code after a fixed duration or at the defined time.
Private Sub Start_Blinking()
'Start blinking
BlinkCell = "A1"
'If the color is red, change the color and text to white
If Range(BlinkCell).Interior.ColorIndex = 3 Then
Range(BlinkCell).Interior.ColorIndex = 0
Range(BlinkCell).Value = "White"
'If the color is white, change the color and text to red
Else
Range(BlinkCell).Interior.ColorIndex = 3
Range(BlinkCell).Value = "Red"
End If
'Wait one second before changing the color again
NextBlink = Now + TimeSerial(0, 0, 1)
Application.OnTime NextBlink, "Start_Blinking", , True
'Stop change
'Application.OnTime NextBlink, "StartBlinking", , False
End Sub
SKILLS 