Lesson#123: How to change cell color with VBA
Here I will show how to change cell color by using VBA. These are the basic lessons for learning VBA.
There are some methods to write code to change the cell background color.
Here I have picked a cell range A1:A10 as an example.
Now press Alt+F11 to go to the VBA editor and click on the sheet you need to place the code. Suppose I pick Sheet2
vbColor method
Ok, there you have to write code.
Range(“A1:A10”).Interior.color = vbBlue
End Sub
and Run the code to see cell color changes into blue.
For a single cell, formatting code should be written like
Sub color1()
Range(“A1”).Interior.color = vbBlue
End Sub
Here is a list of color names when you need to write code in this method. I put the below code in the VBA editor.
Range(“A1”).Interior.color = vbGreen
Range(“A2”).Interior.color = vbBlue
Range(“A3”).Interior.color = vbRed
Range(“A4”).Interior.color = vbBlack
Range(“A5”).Interior.color = vbWhite
Range(“A6”).Interior.color = vbMagenta
Range(“A7”).Interior.color = vbYellow
Range(“A8”).Interior.color = vbCyan
End Sub
RGB method
You can put the color value in Red, Green, or Blue [Example: RGB (123, 23, 78)].
You have to write code like this
Range(“A1”).Interior.color = RGB(0, 0, 0)
Range(“A2”).Interior.color = RGB(255, 0, 0)
Range(“A3”).Interior.color = RGB(0, 255, 0)
Range(“A4”).Interior.color = RGB(0, 0, 255)
Range(“A5”).Interior.color = RGB(255, 255, 255)
Range(“A6”).Interior.color = RGB(255, 255, 0)
Range(“A7”).Interior.color = RGB(255, 0, 255)
Range(“A8”).Interior.color = RGB(0, 255, 255)
End Sub
It shows a result like the above picture. We can use various colors by changing the value of Red, Green, or Blue from 0 to 255.
56 color index method
You can put the color index with the help of the below chart. To make this color chart I have put the code in the VBA editor.
Range(“B1”).Interior.ColorIndex = 0
Range(“B2”).Interior.ColorIndex = 1
Range(“B3”).Interior.ColorIndex = 2
Range(“B4”).Interior.ColorIndex = 3
Range(“B5”).Interior.ColorIndex = 4
Range(“B6”).Interior.ColorIndex = 5
Range(“B7”).Interior.ColorIndex = 6
Range(“B8”).Interior.ColorIndex = 7
Range(“B9”).Interior.ColorIndex = 8
Range(“B10”).Interior.ColorIndex = 9
Range(“B11”).Interior.ColorIndex = 10
Range(“B12”).Interior.ColorIndex = 11
Range(“B13”).Interior.ColorIndex = 12
Range(“B14”).Interior.ColorIndex = 13
Range(“D1”).Interior.ColorIndex = 14
Range(“D2”).Interior.ColorIndex = 15
Range(“D3”).Interior.ColorIndex = 16
Range(“D4”).Interior.ColorIndex = 17
Range(“D5”).Interior.ColorIndex = 18
Range(“D6”).Interior.ColorIndex = 19
Range(“D7”).Interior.ColorIndex = 20
Range(“D8”).Interior.ColorIndex = 21
Range(“D9”).Interior.ColorIndex = 22
Range(“D10”).Interior.ColorIndex = 23
Range(“D11”).Interior.ColorIndex = 24
Range(“D12”).Interior.ColorIndex = 25
Range(“D13”).Interior.ColorIndex = 26
Range(“D14”).Interior.ColorIndex = 27
Range(“F1”).Interior.ColorIndex = 28
Range(“F2”).Interior.ColorIndex = 29
Range(“F3”).Interior.ColorIndex = 30
Range(“F4”).Interior.ColorIndex = 31
Range(“F5”).Interior.ColorIndex = 32
Range(“F6”).Interior.ColorIndex = 33
Range(“F7”).Interior.ColorIndex = 34
Range(“F8”).Interior.ColorIndex = 35
Range(“F9”).Interior.ColorIndex = 36
Range(“F10”).Interior.ColorIndex = 37
Range(“F11”).Interior.ColorIndex = 38
Range(“F12”).Interior.ColorIndex = 39
Range(“F13”).Interior.ColorIndex = 40
Range(“F14”).Interior.ColorIndex = 41
Range(“H1”).Interior.ColorIndex = 42
Range(“H2”).Interior.ColorIndex = 43
Range(“H3”).Interior.ColorIndex = 44
Range(“H4”).Interior.ColorIndex = 45
Range(“H5”).Interior.ColorIndex = 46
Range(“H6”).Interior.ColorIndex = 47
Range(“H7”).Interior.ColorIndex = 48
Range(“H8”).Interior.ColorIndex = 49
Range(“H9”).Interior.ColorIndex = 50
Range(“H10”).Interior.ColorIndex = 51
Range(“H11”).Interior.ColorIndex = 52
Range(“H12”).Interior.ColorIndex = 53
Range(“H13”).Interior.ColorIndex = 54
Range(“H14”).Interior.ColorIndex = 55
Range(“H15”).Interior.ColorIndex = 56
End Sub
Here is the chart for you.
Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.
https://excel-home.ru/articles/excel-ne-rabotaet-ili-ne-otvechaet-na-windows-10/
How to display or view non-printable characters in Excel?
Display of superscript characters in MS EXCEL. In EXCEL, it is easy to format a font to display superscript (x 2 ) and subscript (Al 2 O 3 ) characters. This can be done by selecting a part of the text in the cell and using the Format Cells / Font dialog box ( CTRL + SHIFT + F ) apply the necessary font formatting. But this approach does not work if the cell contains not a value, but a formula. And yet, in some cases, there is a way out.