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 a code to change cell background color.

Here I have picked a cell range A1:A10 as an example.

Now press Alt+F11 to go to 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.

Sub color1()
Range(“A1:A10”).Interior.color = vbBlue
End Sub

and Run the code 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 this below code in VBA editor.

Sub color1()
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, Blue [Example: RGB (123, 23, 78)].

You have to write code like this

Sub color2()
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 this below chart. To make this color chart I have put the code in VBA editor.

Sub color5()
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.

56 color chart

Puspendu is the founder author of Excelabcd. He is a creative person, blogger and Excel-maniac guy.

0 comments on “Lesson#123: How to change cell color with VBA
1 Pings/Trackbacks for "Lesson#123: How to change cell color with VBA"
  1. […] Lesson#123: How to change cell color with VBA […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*