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.


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

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