RGB Color

To make your Excel report more attractive and design, you will need to use colors. In VBA there are two main methods for colouring cells, text, tabs, borders…

We can use the ColorIndex property, which has 56 different colours or the Color method which allows putting any colour through the RGB code (Red-Green-Blue).

ColorIndex Method

The ColorIndex method starts at 1 to 56.

ColorIndex Method

The macro procedure below displays the table above.

Sub SeeColorIndex()
   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, the values go from 0 to 255 for each colour.

Below a few examples of colours :

  • RGB (0, 0, 0): Black
  • RGB (255, 255, 255): White
  • RGB (255, 0, 0): Red
  • RGB (0, 255, 0): Green
  • RGB (0, 0, 255): Blue

We therefore find variations of all colors thanks to this code.

Sub Color()
'Using 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 RGB decomposition in VBA

The purpose here is to recover the RGB decomposition of the colours of a cell. We will, therefore, seek to find the numerical value of red, blue, and green separately. Each value is between 0 and 255.

Sub GetRGBDetails()

    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 the cell flash, you can call the function recursively inside itself as for the factorial mathematical function. Thanks to the OnTime method of the application object, you will set a delay of the process of your code. The OnTime method execute the code after a fixed duration or at a predefined time.

Private Sub StartBlinking()
'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
        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, "StartBlinking", , 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 )

Google photo

You are commenting using your Google 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