# Visual Basic > Visual Basic FAQs >  [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:*

vb Code:
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.

----------


## Spooman

RobDog

Forgive me for posting here about a somewhat unrelated matter, but I am at a loss.
Feel free to remove this post if you so deem.

I started this thread about a week ago 
http://www.vbforums.com/showthread.p...79#post5204679
and have received no replies, so I'm trying here

My issue regards the backcolor of the header row and column.

In your image, the "active cell headers" A and 1 have a gold backcolorIn mine, the *1* (col) and *1* (row) are a slightly darker grey than the default grey.



How can I get mine to be gold the way yours are?

Spoo

----------

