Change Font Color in Excel VBA

Home/Excel VBA/Change Font Color in Excel VBA

Description:

We usually change the font color if we want to highlight some text. If we want to tell the importance of some data, we highlight the text to get the user attention to a particular range in the worksheet. For examples we can change the font color of highly positive figures in to Green or all negative figures in to red color. So that user can easily notice that and understand the data.

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


In this topic we will see how change the font color in Excel VBA. Again, we change the font color in excel while generating the reports. We may want to highlight the font in red if the values are negative, in green if the values are positive. Or we may change font colors and sizes for headings. etc…

Change Font Color in Excel VBA – Solution(s):

We can change the font color by using Font.ColorIndex OR Font.Color Properties of a Range/Cell.

Change Font Color in Excel VBA – Examples

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

'In this Example I am changing the Range B4 Font Color
Sub sbChangeFontColor()
'Using Cell Object
Cells(4, 2).Font.ColorIndex = 3 ' 3 indicates Red Color
'Using Range Object
Range("B4").Font.ColorIndex = 3
'--- You can use use RGB, instead of ColorIndex -->
'Using Cell Object
Cells(4, 2).Font.Color = RGB(255, 0, 0)
'Using Range Object
Range("B4").Font.Color = RGB(255, 0, 0)
End Sub
Instructions:
  1. Open an excel workbook
  2. Enter some data in Ranges mentioned above
  3. Press Alt+F11 to open VBA Editor
  4. Insert a Module for Insert Menu
  5. Copy the above code and Paste in the code window
  6. Save the file as macro enabled workbook
  7. Press F5 to execute itit

Here is an example screen-shot for changing Font Colors:

We can change the font color while working with the reports. But it is good practice to limit to only few colors, instead of using many colors in a single report. In also need to mantian the same color format while delivering the same kind of report next time.

See the following example screen-shot, we are using the same font and background colors for in ranges in the worksheet. It looks good with same king of colors, instead of using multiple colors.

LIMITED TIME OFFER
By |May 11th, 2013|Excel VBA|4 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.

4 Comments

  1. Sitharth May 6, 2015 at 3:44 PM - Reply

    Hi,

    I can trying the same code .. but i can’t see any changes in Excel sheet… and also Not throw any Error Messge … What can i do for this…?
    Thanks advance……

  2. Sitharth May 6, 2015 at 4:00 PM - Reply

    No Problem…. Code is Working Perfectly….

  3. masterji October 19, 2015 at 4:21 PM - Reply

    Thanks for the good information.

  4. Chinchu Joseph September 6, 2016 at 10:31 AM - Reply

    i can trying the same code…….but it shows an error is “object varible not set..” what can i do for this..?

Leave A Comment


Related pages


trim formula exceltrim function in accessinterview question answer for freshermacro writing tutorialadd developer tab to excel 2007vlookup macro in excelexcel unprotect worksheethow to unlock excel spreadsheetvba insert row excelvlookup and hlookup in excelpassword protect a spreadsheethow to delete duplicate rows in excelexcel macro isblankvbscript examples programscomment excel vbawritten test questions and answers for freshersexcel macro open filevba join arraysave workbook vbadaily status report template excelexcel macro stringexcel vba integer rangevb copy fileuserform unload vbavba import data from another excel filehow to delete blank rows in excel 2010excel charts exampleslen excelvba sheet protectionms access msgboxvlookup vs hlookuphow to write a query in excelexcel macro open filelast cell in column vbamsofiledialogfilepicker vbadcl statementduplicate worksheet excelstacked column chart excel 2010excel vba softwarelogical operators vbaexcel vba loopingactivecell.columnvbcrlf vbamacro remove duplicatesexcel vba code listdifferent types of languages in sqlhow do you autofit a column in excelexcel 2007 create drop down listvba variable declarationisblank functionvba createtextfiledefine vlookupvba word textboxunprotect workbook excel 2010how does vlookup work in excel 2013pivot tables tutorialselection pastespecialvba programming basicscheckboxes in excel 2010excel advanced tutorialchart wizard in excel 2007vba case statementsexcel templates project managementdml querieswrite macros in excelexcel vba saveshortcut key to open excelexcel vlookup examplevba recordaccess vba close formnested if statement in excel 2010