[FAQ's: OD] How do I color individule parts of a cell/range with separate colors?
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.
http://www.vbforums.com/images/ieimages/2007/02/1.jpg
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
Re: [FAQ's: OD] How do I color individule parts of a cell/range with separate colors?
Your "Red Green Blue" look quite weird and actually appear as "Blue Green Red" (at least on your picture).
Re: [FAQ's: OD] How do I color individule parts of a cell/range with separate colors?
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.
Re: [FAQ's: OD] How do I color individule parts of a cell/range with separate colors?
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.
1 Attachment(s)
Re: [FAQ's: OD] How do I color individule parts of a cell/range with separate colors?
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 backcolor
- In mine, the 1 (col) and 1 (row) are a slightly darker grey than the default grey.
Attachment 151135
How can I get mine to be gold the way yours are?
Spoo