[FAQ's: OD] How do I color individule parts of a cell/range with separate colors?

[RobDog888]

Many times you want to color only part of a cells contents to identify some criteria or just to bring attention to something without changing the entire cells color. One way to do this programmatically is to use the Characters collection and pass the arguments of the starting and ending range. Then its just applying the color to that range and viola! Multiple colors in a single cell/range.






Excel 2000-2007 and VB.NET 2003/2005 Code Example:

Option Explicit On
Option Strict On
'Add a reference to MS Excel xx.0 Object Library (COM)
Imports Microsoft.Office.Interop
 
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oApp As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
        Dim oWB As Excel.Workbook = DirectCast(oApp.Workbooks.Add(), Excel.Workbook)
        Dim oSht As Excel.Worksheet = DirectCast(oWB.Sheets("Sheet1"), Excel.Worksheet)
        Dim oRange As Excel.Range = DirectCast(oSht.Cells(1, 1), Excel.Range)
        oApp.Visible = True
        'Add some text
        oRange.FormulaR1C1 = "Red Green Blue"
        'Make sure the entire cell color is black to start (just for fun lol)
        oRange.Characters(Start:=1, Length:=0).Font.ColorIndex = Excel.Constants.xlAutomatic
        'Apply the color to the first word
        oRange.Characters(Start:=1, Length:=3).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Red)
        'Apply the color to the second word
        oRange.Characters(Start:=5, Length:=5).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Green)
        'Apply the color to the third word
        oRange.Characters(Start:=11, Length:=4).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Blue)
        oRange.Columns.AutoFit()
        oRange = Nothing
        oSht = Nothing
        'Optionally save and close the workbook (uncomment next line)
        'oWB.Close(SaveChanges:=True, Filename:="C:\Users\VB-Guru\Documents\Book1.xls",RouteWorkbook:=False
        oWB = Nothing
        'Optionally quit Excel (uncomment next line)
        'oApp.Quit()
        oApp = Nothing
    End Sub
 
End Class

[RhinoBull]

Your "Red Green Blue" look quite weird and actually appear as "Blue Green Red" (at least on your picture).

[RobDog888]

Thanks RB. Turns out that the Color.Red.ToArgb() is not th correct translation for what the .Font.Color property is expecting. I updated the code example and image. Using System.Drawing.ColorTranslator.ToOle(Color.Red) produces 255 which is the proper value for Red and not -65536 as the .ToArgb() does. The Alpha channel just messes things up.

[RhinoBull]

ColorTranslator.ToOle was my first thought as I did use it in the past but I wasn't sure if that would work with Office so I didn't even mention it.