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


excel vba copy destinationvba selection.deleteexcel mergingvba autofilterhow to insert column in excelexcel formula correctorconnect excel to oracle databasesample gantt chart templatespss sqlvba save as csvexcel background color formulavba comment codeexcel cells.findmessage in vbavba file objectmacro to insert rows between datavba save excelexcel insert rowsexcel macro hide columnexcel vba data entry form examplesexcel vba select columnsexcel 2007 userforminsert comment excelcopy and paste cells in excelhow to find duplicate records in exceldcl tclvba create command buttoncreate excel vbaschedule excel macroexcel concatenateexcel macros how toapplication.filedialogvba vbcrlfvba switch casepivots excelmicrosoft estimate templateautofit cells in excelcommand button code in excelif statements excelmacro filter excelexcel input form templatevba excel to powerpointexcel aptitude test questions and answersarray vba excelvba beginners tutorialexcel delete all blank rowsvba excel dynamic arrayaccess vba export query to excelexcel macro hide columnshow to delete empty rows in excel 2007vba ebookcountif function on excelvba code vlookupdml & ddlvba userformsvba listbox userformexcel macro programinsert drop down box in excel 2007excel checkbox valuevba tutorvba print functionexcel duplicate countexcel merge sheets by columnscripting filesystemobject opentextfilehow to put a password on a spreadsheetexcel vba columnsexcel automatic sorthlook upudf vbaexcel to csv converterexcel macro codeprotect excel macrohow to delete a row in excelexcel vba rowshow to delete a sheet in excelproject tracker template excelbypass excel password