Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Coloring half a cell... (EXCEL 97/2000)

    I'm trying to color half a cell (the lower left part). When looking at borders in the cell properties, I can see that one can apply a diagonal-'border' to a cell. The remaining part of the problem however, coloring that half of the cell, seems a bit more of a challenge.

    Of course, one could do it different, and place a shape just over the active-cell. Which is what I did (see -stripped- code):
    <font color=blue>
    Sub AddRectangle
    Dim Colr As Integer, TC As Integer
    'Uses cell-value to determine color
    Colr = ActiveCell.Value
    Select Case Colr
    Case 6 'Half-green
    TC = 11
    Case 7 'Half-Yellow
    TC = 13
    Case 8 'Half-Red
    TC = 10
    Case 9 'Half-Blue
    TC = 44
    Case Else
    TC = 0
    End Select
    '
    ActiveSheet.Shapes.AddShape(msoShapeRightTriangle, ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = TC
    ActiveCell.Select
    End Sub
    </font color=blue>

    Of course this is much less elegant than coloring half the cell without having to use a shape...

    (1) Who knows of an alternate way of doing this (so without using shapes)?

    (2) Supposing the above method (using a shape) is the only way, I still have a problem: if I'd apply the code above AGAIN to a cell, then obviously I would want to remove any shape attached to the cell FIRST (now the code just puts another shape on top). I have no clue how I could select and delete such a shape however....

    Any help would be appreciated!

    Thanks, Erik Jan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Coloring half a cell... (EXCEL 97/2000)

    Perhaps one of the Excel gurus will come up with a better method, but I don't know of a way to color the interior of the cell itself the way you want.

    If the shape hasn't been moved away, you could use the following code to delete it:

    Dim i As Long
    For i = ActiveSheet.Shapes.Count To 1 Step -1
    With ActiveSheet.Shapes(i)
    If .Top = ActiveCell.Top And .Left = ActiveCell.Left Then
    .Delete
    End If
    End With
    Next i

    This will delete any shape whose top left corner coincides with that of the active cell. If you like, you can check for width and height too.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coloring half a cell... (EXCEL 97/2000)

    I dont think that you can colour half a cell.
    If you name your shapes when you create them (name them with their row/column coordinates perhaps?) then you could just change their colour rather than deleting and recreating them.

    HTH

    Peter

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coloring half a cell... (EXCEL 97/2000)

    Thanks,

    That is so obvious I didn't even think about it.... <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> great suggestion and probably the best way to do it!

    I'll use it if no other suggestions (to do it without shapes) appear!

    Erik Jan

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coloring half a cell... (EXCEL 97/2000)

    I did think about that, however sometimes I also have to insert rows and/or columns... that would mess-up this scheme... thanks for your suggestion anyway!

Posting Permissions

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