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).
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
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 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, "StartBlinking", , True 'Stop change 'Application.OnTime NextBlink, "StartBlinking", , False End Sub