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.

The macro procedure below displays the table above.

Sub SeeColorIndex()

'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, 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

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

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.

'If the color is red, change the color and text to white