Change Background Color of Cell Range in Excel VBA

Home/Excel VBA/Change Background Color of Cell Range in Excel VBA

Description:

It is an interesting feature in excel, we can change background color of Cell, Range in Excel VBA. Specially, while preparing reports or dashboards, we change the backgrounds to make it clean and get the professional look to our projects.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Change Background Color of Cell Range in Excel VBA – Solution(s):

Delete Worksheet in Excel VBA
We can use Interior.Color OR Interior.ColorIndex properties of a Rage/Cell to change the background colors.

Change Background Color of Cell Range in Excel VBA – Examples

The following examples will show you how to change the background or interior color in Excel using VBA.

Example 1

In this Example below I am changing the Range B3 Background Color using Cell Object

Sub sbRangeFillColorExample1()
'Using Cell Object
Cells(3, 2).Interior.ColorIndex = 5 ' 5 indicates Blue Color
End Sub
Example 2

In this Example below I am changing the Range B3 Background Color using Range Object

Sub sbRangeFillColorExample2()
'Using Range Object
Range("B3").Interior.ColorIndex = 5
End Sub
Example 3

We can also use RGB color format, instead of ColorIndex. See the following example:

Sub sbRangeFillColorExample3()
'Using Cell Object
Cells(3, 2).Interior.Color = RGB(0, 0, 250)
'Using Range Object
Range("B3").Interior.Color = RGB(0, 0, 250)
End Sub
Example 4

The following example will apply all the colorIndex form 1 to 55 in Activesheet.

Sub sbPrintColorIndexColors()
Dim iCntr
For iCntr = 1 To 56
Cells(iCntr, 1).Interior.ColorIndex = iCntr
Cells(iCntr, 1) = iCntr
Next iCntr
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute the procedure
  7. You can see the interior colors are changing as per our code

Change Background Color of Cell Range in Excel VBA – Download: Example File

Here is the sample screen-shot of the example file.

Download the file and explore how to change the interior or background colors in Excel using VBA.

mongopono.ru – ColorIndex

LIMITED TIME OFFER
By |February 9th, 2013|Excel VBA|2 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

2 Comments

  1. Harbinder Singh April 13, 2017 at 12:23 PM - Reply

    need help in excel

    In company there are 10000 employee and all having duty in A shift, B shift, C shift. Each shift of 08 hrs

    i have to find those employee who work continuous 10 days or more then 10 days

    please help me to find the solution using VB macro in excel or any other way to find from huge no of data in excel

    Kindly provide me the solution.

  2. RandomOne May 23, 2017 at 5:46 AM - Reply

    Harbinder Singh
    its dependes of your database, can check with a vba code for filter based on the criteria, select the range and then color it,
    Regards!

Leave A Comment


Related pages


excel export to txtexcel vba backcolorlcase vbaexcel vba copyfromrecordsetsample pivot tablesdynamic pivot tablesvba open documentinterview aptitude questions and answers with explanationexcel vba filefilterexcel colour indexhow to unlock formulas in excelxml file to excel converterunhide spreadsheetoffset excel functionexcel as database in vbahow to put drop down list in excel 2007vlookup and hlookup in excelexcel programming vbacombine multiple worksheets into one workbookcheck activexvba classes tutorialunhide all cells in excelexcel based dashboardsexample excel formulasprotect sheet excelexcel cell function colorremoving duplicate data in excelconsolidate worksheetsvba change cell coloroffice 2007 excel templatesmaking dashboards in excelhlookup excel 2010 exampleactiveworkbook vbahow to shift columns in excelconvert from excel to xmlexcel isblank functionexcel datevaluevba containsvba word textboxexcel vba populate comboboxdifference between operand and operatorend vbaexcel project timeline charthyperlink shortcut keyvlookup function explainedcall vba functionvba excel case statementvba word userformconstruction estimating spreadsheet templatevba access database connection examplevlookup between two sheetsvbscript carriage returncounting duplicates in excelexcel option button valuesql ddl dml dcl commandsvba excel variableinterview common questions and answers for fresherssql queries interview questions and answers pdf for freshershorizontal lookup excelto unprotect excel sheetbeautiful excel dashboardsexcel 2007 checkboxexcel function sumifworksheets vba exceliserror excel functionexcel sheet shortcut keyshow to unlock macro password in excelhow to add developer tab to the ribbonrefresh excel spreadsheetvlookup to the leftaccess macro tutorialhow to protect an excel sheetexcel construction estimate templateexcel enable macroworkbook window definitionhow to highlight duplicate values in excelvb interview questions and answers pdfworksheetfunction sumvba empty cell