Help with Excel Macro

Nick Kaufman

protector of human kind from spoilers
Lifetime Member
SoSH Member
Aug 2, 2003
12,117
A Lost Time
So I am trying to create cells split in two background colors. Problem is that Excel is using a gradient effect and it doesn't look good. I want two solid colors.

After googling for a while, I found a guy facing a similar problem which he solved by using a macro. His code is this:

With Selection.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 225
.Gradient.ColorStops.Clear
End With

With Selection.Interior.Gradient.ColorStops.Add(0)
.Color = RGB(255, 0, 0)
.TintAndShade = 0
End With

With Selection.Interior.Gradient.ColorStops.Add(0.49999999)
.Color = RGB(255, 0, 0)
.TintAndShade = 0
End With

With Selection.Interior.Gradient.ColorStops.Add(0.5)
.Color = RGB(0, 255, 0)
.TintAndShade = 0
End With

With Selection.Interior.Gradient.ColorStops.Add(1)
.Color = RGB(0, 255, 0)
.TintAndShade = 0
End With
https://superuser.com/questions/1368835/can-i-create-a-multi-color-cell-without-a-gradient-in-excel

Thing is I have no clue about macros, so after googling a bit more I came across this page which gave instructions on how to insert the code into the file. I followed those instructions, but I can't get to remove the gradient effect.

What am I missing?

PS. FWIW, I am rather lost on this part, since the code I copied contained none of the things this person refers to:
If the code of your VBA macro does not contain the following lines in the beginning:


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual



Then add the following lines to get your macro to work faster (see the screenshots above):


  • To the very beginning of the code, after all code lines that start with Dim (if there are no "Dim" lines, then add them right after the Sub line):
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
  • To the very of the code, before End Sub:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 

Bergs

Member
SoSH Member
Jul 22, 2005
14,887
PS: Also, conditional formatting can be tricked into doing this, depending on the specific use case.
 

Nick Kaufman

protector of human kind from spoilers
Lifetime Member
SoSH Member
Aug 2, 2003
12,117
A Lost Time
Did you actually run the macro? (Alt-F8), run
I actually tired that, but it gives me a screen with all the available macros and none are shown. I guess something is off with the code or with the way I added it, but I am completely clueless about it.
 

Bergs

Member
SoSH Member
Jul 22, 2005
14,887
I actually tired that, but it gives me a screen with all the available macros and none are shown. I guess something is off with the code or with the way I added it, but I am completely clueless about it.
PM me and as long as it's not sensitive info, you can email it to me and I'll fix it for you tomorrow.
 

Nick Kaufman

protector of human kind from spoilers
Lifetime Member
SoSH Member
Aug 2, 2003
12,117
A Lost Time
PS: Also, conditional formatting can be tricked into doing this, depending on the specific use case.
The other solution I 've found involves using shapes, but it creates an additional headache with the use of text boxes and it isn't precise.

If there's another way, i '' ll give it a try.