Results 1 to 5 of 5

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

  1. #1

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

    [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 03: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!
    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 i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2

  3. #3

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

    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!
    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 i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4

  5. #5
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    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.


    Name:  excel5.png
Views: 806
Size:  3.7 KB

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

    Spoo

Posting Permissions

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



Click Here to Expand Forum to Full Width