VBA Colors

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.

ColorIndex Method

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:

RedGreenBlueResult Color
000Black
255255255White
25500Red
02550Green
00255Blue
2552550Yellow
2550255Magenta
0255255Cyan

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
Red Green Blue method RGB

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s