Excel: How do I insert different colors in same cell?-VBForums
Results 1 to 10 of 10

Thread: Excel: How do I insert different colors in same cell?

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2006
    Posts
    14

    Excel: How do I insert different colors in same cell?

    Hi

    I need to be able to display one or more colors within a
    cell in Excel.

    Ultimately it would be nice to also be able to put in a number
    within each color in the cell as well.

    (Note: I would like to insert one or more uniform square or rectangular color in the same cell. Optionally with some text within the colored square/reqtangular shape.)

    I'm hoping I can use one or more object or something else to
    represent the colors. If that is not possible then maybe some other solution.

    I must be able (using VBA) to check what colors that currently exist within a given cell.

    I also need to create the colored object(s) using VBA.

    Would be nice if I could do this without having to use premade images or whatever.... hope I can do this just by using VBA.

    Anybody with a clue on how I do that.
    Last edited by bongobongo; Jun 12th, 2006 at 01:44 PM.

  2. #2
    Frenzied Member zaza's Avatar
    Join Date
    Apr 2001
    Location
    Borneo Rainforest Habits: Scratching
    Posts
    1,484

    Re: Excel: How do I insert different colors in same cell?

    Welcome to the Forums


    Excel can only display one colour at any given time within any given cell. So your options are:

    Use more than one cell to represent a "cell". Why does it have to be an individual cell? Could 4 Excel cells represent one "cell", for example? This is easily accomplished in VBA.

    Use objects such as pictureboxes or labels and position them within the cell. This will be very much of a nuisance, because positioning them in the first place will be difficult, they won't scale or size properly if you adjust the cells. This method is also relatively easy to set up in VBA, but more difficult to do anything with afterwards.


    Could you elaborate on why you need this, so that we can put it into perspective and figure out what you're after?

    zaza
    I use VB 6, VB.Net 2003 and Office 2010



    Code:
    Excel Graphing | Excel Timer | Excel Tips and Tricks | Add controls in Office | Data tables in Excel | Gaussian random number distribution (VB6/VBA,VB.Net) | Coordinates, Vectors and 3D volumes

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2006
    Posts
    14

    Re: Excel: How do I insert different colors in same cell?

    Hi.

    I cannot use multiple cells to reprecent one cell, unless I can create multiple
    cells within another cell..... probably not very doable though... :-)

    Pictureboxes and labels.... maybe, have to look into that.

    Is there any other options..

    Have looked through all the font characters and found some squares that might be usable, since they are characters I can set the color..... so allmost there but this will not enable me to get a square colored box with a number within...

    I change the character color like this:

    With ActiveCell.Characters(Start:=1, Length:=1).Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = 46
    End With

    But then I wonder if there is an attribute for a character where I'm able to store a number?

  4. #4
    Frenzied Member zaza's Avatar
    Join Date
    Apr 2001
    Location
    Borneo Rainforest Habits: Scratching
    Posts
    1,484

    Re: Excel: How do I insert different colors in same cell?

    Why can you not use multiple cells to represent one cell?
    I use VB 6, VB.Net 2003 and Office 2010



    Code:
    Excel Graphing | Excel Timer | Excel Tips and Tricks | Add controls in Office | Data tables in Excel | Gaussian random number distribution (VB6/VBA,VB.Net) | Coordinates, Vectors and 3D volumes

  5. #5
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,370

    Re: Excel: How do I insert different colors in same cell?

    you could do something like this?

    looks like 3 per cell ....
    Attached Images Attached Images  
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2006
    Posts
    14

    Re: Excel: How do I insert different colors in same cell?

    Well, I'm not able to do that because:

    A cell higher up in the colum are related to the color(s) set for zero, one or more cells further down, in the same column.

    I might be able to do what I want using some special characters, but then it would be nice being able to be able to create a custom attribute for the characters to be used.

    Is it possible to create one or more custom attribute(s) for either the inserted character(s) (which actually looks like colored squares), or eventually some attribute for the cell that I could use to store data related to each of the inserted characters in the cell?

    Example:
    When I insert a character (colored square) in a cell I also want to attach at least one, and hopefully two short strings to the character, that I can fetch anytime laiter. If not possible to create custom attributes for a character then I might be able to store the extra string(s) in an attribute for the cell.

  7. #7
    Frenzied Member zaza's Avatar
    Join Date
    Apr 2001
    Location
    Borneo Rainforest Habits: Scratching
    Posts
    1,484

    Re: Excel: How do I insert different colors in same cell?

    Not ideal to do using custom characters, because they are effectively just strings. You would need to append cells to each row telling you what the characters mean for that row.
    I still have no idea what it is that you're trying to accomplish - some of us may be able to come up with still different (and possibly better) ways of going about it if you tell us what the objective is rather than your way of going about the problem.


    I won't steal it, I promise.
    I use VB 6, VB.Net 2003 and Office 2010



    Code:
    Excel Graphing | Excel Timer | Excel Tips and Tricks | Add controls in Office | Data tables in Excel | Gaussian random number distribution (VB6/VBA,VB.Net) | Coordinates, Vectors and 3D volumes

  8. #8

    Thread Starter
    New Member
    Join Date
    Jun 2006
    Posts
    14

    Re: Excel: How do I insert different colors in same cell?

    Is it possible to create one or more custom attributes for a cell, where each of the custom attribute should be able to store a short text string?

  9. #9
    New Member
    Join Date
    Apr 2010
    Posts
    1

    Smile Re: Excel: How do I insert different colors in same cell?

    Wakey Wakey, I'm sure you're all asleep and have long lost interest in this post however... for multiple fonts/colours etc in a single cell you use the cells(x,y).characters(start,length).font function

    The following code makes the first 10 characters BOLD and colour 56 and the rest not bold and colour 12

    With Cells(row,col).Characters(start:=1, Length:=10).Font
    .FontStyle = "Bold"
    .ColorIndex = 56
    End With

    With Cells(col,row).Characters(start:=11, Length:=180).Font
    .FontStyle = "Normal"
    .ColorIndex = 12
    End With

  10. #10
    Frenzied Member kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    1,990

    Re: Excel: How do I insert different colors in same cell?

    Almost a year after last post but thank you. This was really useful. The point of these forums after solving the problem for the original poster is to help others.

    So here is my solution, it may help you.

    What I needed was a way to code the contents of the cell. Each color ment a different location of the inventory, but I needed that to be on one cell. Since there are only 3 different locations I made a sub that cycles through the options:


    vb Code:
    1. Sub colorChange()
    2.  
    3. Dim color1, color2 As Integer
    4.  
    5. color1 = ActiveCell.Characters(1, 1).Font.ColorIndex
    6. color2 = ActiveCell.Characters(2, 1).Font.ColorIndex
    7.  
    8. Select Case color1 + color2
    9.     Case 7
    10.         color1 = 5
    11.     Case 8
    12.         color1 = 4
    13.         color2 = 5
    14.     Case 9
    15.         color2 = 3
    16.     Case Else
    17.         color1 = 4
    18.         color2 = 5
    19. End Select
    20.  
    21. ActiveCell.Characters(1, 1).Font.ColorIndex = color1
    22. ActiveCell.Characters(2, 1).Font.ColorIndex = color2
    23. ActiveCell.Characters(3, 1).Font.ColorIndex = 3
    24.  
    25. End Sub


    * NOTE1: color 3 is always red since it is only relevant if there are 3 characters on the cell
    * NOTE2: colors 1 and 2 cycle from Green/Blue to Green/Red to Blue/Red. No other combination is necesary.
    * NOTE3: this works on the current cell, I asigned the macro to a key combination.
    Last edited by kaliman79912; Mar 2nd, 2011 at 05:37 PM.
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

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.