Split one cell in the middle and fill each with one color

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can I in Excel split one cell into 2 halfs and have each half filled wit a
seperate color?
 
You could do it to two adjacent cells (narrow column) to achieve the
effect, but not to a single cell.

Hope this helps.

Pete
 
Hi

You can't split a cell.
You could give the appearance of a split cell by using the drawing Toolbar,
selecting the rectangle and creating two identically sized rectangles which
just covered one cell.
Right click on each rectangle and use Format Autoshape to the colour you
wanted for each one.
 
Here is how to put two colors into a cell. This does not allow you to create two cells from one information wise. You create the appearance of two pieces of information though using font formatting and spacing.

  1. Right click on the the cell(s)
  2. Select "Format cells"
  3. Click on "Fill" tab
  4. Click on "Fill Effects ..." (below color grid and above "Sample")
  5. Select the two colors you desire
  6. Select "Diagonal Up" or "Diagonal Down" under "Shading Styles"
However this produces blending of the two colors. To remove the blending you can use vba.

Here is how I accomplished it with VBA

Code: 
Sub Macro1()
'
' Macro1 Macro
'
    With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
    End With
End Sub

If you want to get rid of the blending add 2 ColorStops to the code like this. Note how the color now switches between 49% and 51% of the cell. I also removed the .TintAndShade lines as they are not important.

Code: 
Sub Macro1()
'
' Macro1 Macro
'
    With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorDark1 'First Color
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0.49)
        .ThemeColor = xlThemeColorDark1 'First Color
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0.51)
        .ThemeColor = xlThemeColorAccent1 'Second Color
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent1 'Second Color
    End With
End Sub

A couple of final notes.
  1. If you are looking for a diagonal or vertical color separation simply change the .Gradient.Degree to the appropriate angle.
    1. 0 = Vertical
    2. 45= rising diagonal
    3. 135 = falling diagonal
  2. Try .Color instead of .ThemeColor with a line of code like this
    1. .Color = RGB(255, 206, 0)
    2. This makes it easy to get whatever color you want all you need are the RGB integer values. Look them up on line or using excel under the More Colors> Custom colors section.
 
This vba is really helpful, especially eliminating the gradient, but I have cells which have two color's already predefined using conditional formatting. How do I make sure the color stays as per the conditional formatting instead of a theme color or a fixed color using RGB's, but without the gradient?
 
Do you mean like this? Two colours separated from each other, vertically?

If yes, check out the following link:
 
Hi Sean,
That is exactly what I want. Unfortunately, you don't explain exactly how to do it. Can you please do that in a really simple way?
Thanks
 
Back
Top