[FAQ's: OD] How do I color individule parts of a cell/range with separate colors?-VBForums
Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,089

    [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.






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

    vb Code:
    1. Option Explicit On
    2. Option Strict On
    3. 'Add a reference to MS Excel xx.0 Object Library (COM)
    4. Imports Microsoft.Office.Interop
    5.  
    6. Public Class Form1
    7.  
    8.     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    9.         Dim oApp As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
    10.         Dim oWB As Excel.Workbook = DirectCast(oApp.Workbooks.Add(), Excel.Workbook)
    11.         Dim oSht As Excel.Worksheet = DirectCast(oWB.Sheets("Sheet1"), Excel.Worksheet)
    12.         Dim oRange As Excel.Range = DirectCast(oSht.Cells(1, 1), Excel.Range)
    13.         oApp.Visible = True
    14.         'Add some text
    15.         oRange.FormulaR1C1 = "Red Green Blue"
    16.         'Make sure the entire cell color is black to start (just for fun lol)
    17.         oRange.Characters(Start:=1, Length:=0).Font.ColorIndex = Excel.Constants.xlAutomatic
    18.         'Apply the color to the first word
    19.         oRange.Characters(Start:=1, Length:=3).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Red)
    20.         'Apply the color to the second word
    21.         oRange.Characters(Start:=5, Length:=5).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Green)
    22.         'Apply the color to the third word
    23.         oRange.Characters(Start:=11, Length:=4).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Blue)
    24.         oRange.Columns.AutoFit()
    25.         oRange = Nothing
    26.         oSht = Nothing
    27.         'Optionally save and close the workbook (uncomment next line)
    28.         'oWB.Close(SaveChanges:=True, Filename:="C:\Users\VB-Guru\Documents\Book1.xls",RouteWorkbook:=False
    29.         oWB = Nothing
    30.         'Optionally quit Excel (uncomment next line)
    31.         'oApp.Quit()
    32.         oApp = Nothing
    33.     End Sub
    34.  
    35. End Class
    Last edited by RobDog888; Mar 4th, 2007 at 02:51 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  2. #2

  3. #3

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,089

    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.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  4. #4

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.