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 find duplicate values in columnbest excel vba bookexcel unhide alluse of vlookup function in excelselect column excel vbapassword protect vbaexcel horizontal lookupexcel vba programming tutorial pdfvba call macroexcel vba offsetlistbox in userform excel vbaexcel vba referencevba sample projectsunprotect workbook without passwordvba query table3d bubble chart excel 2010excel vba instr functionexcel formula hlookup exampleexcel vba arraymso file viewerexcel vba procedureadding macro to excelaccess vba for loopexcel vba templatesvba programming examplesvba insert multiple rowsexcel export to txthow to insert yes no box in excelopen excel from access vbasample excel macroshard return excelcalculator codes in visual basicunprotect excel sheet without passwordinsert new worksheet excel 2007excel vba project examplesexcel insert columnsdatabase management system interview questions and answers pdfddl commands in dbmsseriescollection vbato unprotect excel sheetvb command buttonhow to find difference between two dates in excelexcel vba column rangehow to unmerge cellsexcel vba delete pivot tablems excel offsethyperlink codingexcel 2010 macro securityexcel count duplicatevba add hyperlinkexcel replace in formulaautofit on excelproject portfolio dashboard template excelexcel vlookup functionsvba access database connection examplechoose function in excelhow to unlock a password protected excel workbookworkbooks activateoffset function in vbaexcel copy worksheetremove blank excel rowsvba listboxopen userform vbamacros in excel tutorial for beginners pdfqlikview delete rowsvba pivottableexcel hide cellsvba excel cell colorexcel count duplicatedata definition language ddl commandapplication.getsaveasfilename vbaexcel xml examplevba tutorial excel