Invert if negative chart error

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
Hi I am using these steps
http://www.andypope.info/charts/Invertneg.htm
to fill color if negative for my bar graph. But it works only when sheet is active. When when I close that excel workbook and open again. color of bar chart change automatically from red and green to blue and white. I could not figure out why color change automatically whenever I open my sheet again. I was trying to call following sub when sheet become active but that give error 1004 in line 2. I was trying to assign macro to chart. But that works only if I select chart. Is there any solution
Code:
'Formating of Financial saving based on current production level
Public Sub ColorBar()
    [COLOR="Red"]ActiveSheet.ChartObjects("Chart 64").Activate[/COLOR] ' error here
    ActiveChart.SeriesCollection(1).Select
    Selection.InvertIfNegative = True
    Selection.Fill.Patterned Pattern:=msoPattern5Percent
    With Selection
        .Fill.ForeColor.SchemeColor = 43
        .Fill.BackColor.SchemeColor = 3
    End With
    With Selection.Interior
        .ColorIndex = 3
        .PatternColorIndex = 43
        .Pattern = xlSolid
    End With
End Sub
http://www.excelforum.com/excel-programming/739390-invert-if-negative-chart-issue.html#post2353397
 
Last edited:

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You shouldn't need to activate or select anything. Try the untested:

Code:
Public Sub ColorBar()
    Dim Sh As Worksheet
    Dim Cht As Chart
'***Change sheet reference to suit***
    Set Sh = Worksheets("Sheet1")
    Set Cht = Sh.ChartObjects("Chart 64").Chart
    With Cht.SeriesCollection(1)
        .InvertIfNegative = True
        .Fill.Patterned Pattern:=msoPattern5Percent
        .Fill.ForeColor.SchemeColor = 43
        .Fill.BackColor.SchemeColor = 3
        With .Interior
            .ColorIndex = 3
            .PatternColorIndex = 43
            .Pattern = xlSolid
        End With
    End With
End Sub
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
As far as the colour changes are concerned I can't help, but as far as the error on line two try the following (untested) which should allow changes to take place without it being the activesheet, but you have to adjust the macro to match your sheet's name (red):
Code:
Public Sub ColorBar()
With Sheets("[COLOR=Red]TheSheetName[/COLOR]").ChartObjects("Chart 64").Chart.SeriesCollection(1)
  .InvertIfNegative = True
  .Fill.Patterned Pattern:=msoPattern5Percent
  .Fill.ForeColor.SchemeColor = 43
  .Fill.BackColor.SchemeColor = 3
  With .Interior
    .ColorIndex = 3
    .PatternColorIndex = 43
    .Pattern = xlSolid
  End With
End With
End Sub
 

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
that give error Unable to set invertifnegative property of series class.
Rich (BB code):
Public Sub ColorBar()
    Dim Sh As Worksheet
    Dim Cht As Chart
'***Change sheet reference to suit***
    Set Sh = Worksheets("Sheet1")
    Set Cht = Sh.ChartObjects("Chart 64").Chart
    With Cht.SeriesCollection(1)
        .InvertIfNegative = True ' error here
        .Fill.Patterned Pattern:=msoPattern5Percent
        .Fill.ForeColor.SchemeColor = 43
        .Fill.BackColor.SchemeColor = 3
        With .Interior
            .ColorIndex = 3
            .PatternColorIndex = 43
            .Pattern = xlSolid
        End With
    End With
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Do you also get an error with p45cal's code?
 

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
My sheet is protected..when I have removed protection ..your code is working. But I need sheet protected.
I have tried following code for sheet activation . But still no success
Code:
Private Sub Worksheet_Activate()
Me.Protect userinterfaceonly:=True
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Me.Protect userinterfaceonly:=True ' for protection
Me.ScrollArea = "A1:T51"
Me.Range("A1:T1").Select
ActiveWindow.Zoom = True
Me.Range("A3").Select
Call ColorBar
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

If that's not working you will need to unprotect the sheet before calling ColorBar and protect it again afterwards.
 

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
I have one problem again. These are red and green colors. When I open in excel 2003 colors are now red and green. these do not change to blue and white default color. But when I open in excel 2007 color change to red and white instead of red and green. red becomes white and green becomes red in 2007
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,774
Messages
5,513,357
Members
408,948
Latest member
robertjohn18

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top