Results 1 to 4 of 4
  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 (Excel2003 VBA)

    I'm looking to color half a cell (the left or the right side). I know there are tricks in Excel itself but as far as I've seen, nothing comes close to normal shading (and yes, I think I know this is easier with Excel 2007).

    So, I'm thinking of doing with with VBA. No very complex I think... make a graphical element and align it to the cell left or right. Make the graphical element the same height and half as wide as the underlying cell, give it a color, remove the borders and bingo (I think and hope...).

    Of course I can try and build this but quite frankly, I'm hoping that some code is already available that does this (or comes close)... someone must have done this before!

    I'd appreciate any suggestions and will post back my working code here after I'm done.

    Oh, by the way, I'd need to have a way to select a cell, and via worksheet.change event find if a graphical element is already there and either update or remove that... To be honest, if I think about it, that seems the more difficult part of the code: how do I identify a graphical element that is on top of the current cell?

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

    Re: Coloring half a cell (Excel2003 VBA)

    You could create a shape whose Top, Left and Height properties are equal to those of a cell, and whose Width is half that of the cell.

    About your second question: a Shape object has a TopLeftCell property; this is a Range that represents the cell that lies under the upper left corner of the shape. So you could loop through all shapes and stop when the TopLeftCell equals the "current cell".
    Or you could name the shapes after the cells they cover, i.e. a shape on top of cell B4 is named "B4".

  3. #3
    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 (Excel2003 VBA)

    I created this code and it works for what I need:

    <pre>Sub HalfCell(CellRange As Range, Switch As Integer)
    '
    'Switch=0 Erases graphical element on cell
    'Switch<0 Covers left side of cell with graphical element
    'Switch>0 Covers right side of cell with graphical element
    '
    For Each c In CellRange
    RemShape c 'remove shape first
    Select Case Switch
    Case Is < 0
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, c.Left, c.Top, c.Width / 2, _
    c.Height).Select
    Case Is > 0
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, c.Left + c.Width / 2, c.Top, _
    c.Width / 2, c.Height).Select
    End Select
    If Switch <> 0 Then
    With Selection
    .ShapeRange.Fill.Visible = msoTrue
    .ShapeRange.Fill.Solid
    .ShapeRange.Fill.ForeColor.SchemeColor = 42
    .ShapeRange.Fill.Transparency = 0.5
    .ShapeRange.Line.Visible = msoFalse
    .PrintObject = True
    .Name = "myShape_" & c.Address
    End With
    End If
    Next
    CellRange.Select
    End Sub

    Sub RemShape(CellShape As Range)
    On Error Resume Next
    ActiveSheet.Shapes("myShape_" & CellShape.Address).Delete
    On Error GoTo 0
    End Sub

    </pre>


  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coloring half a cell (Excel2003 VBA)

    Muy neat!

Posting Permissions

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