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.

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:
Font Colors in Excel VBA

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.

Font Colors in Excel VBA Example

Premium Project Management Templates

120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: May 11th, 2013Categories: Excel VBATags: ,

Share This Story, Choose Your Platform!

About the Author: Valli

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

    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

    No Problem…. Code is Working Perfectly….

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

    Thanks for the good information.

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

    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