0

I have two Excel sheets where 6 of the 30 columns have databars in them. They all use the same code as below.

But two of the columns in one of the sheets do not display the databar. When I look at Manage Rules in conditional formatting, I do see the databar there.

Then I create several files from that sheet based on different sorts - product, region, etc. and these files also inconsistently do not show the databar.

I am using Excel 2010 32-bit on Windows 7 64-bit.

What am I doing wrong? Thanks in advance for your help.

Public Function formatDatabar(wbkO As Workbook, wks As Worksheet, colNo As Integer)

Dim i As Integer, ctr As Integer, col As Integer

wbkO.Activate
Set wks = wbkO.Worksheets(wks.Name)
wks.Activate
ctr = findLastRow(wks.Name)
col = findLastCol(wks.Name)

wbkO.Activate
wks.Activate
wks.Range(wks.Cells(5, 1), wks.Cells(ctr, col)).Select

With Selection
    .Cells.Font.Size = "8"
    .Cells.Font.Bold = False
    .Cells.Font.Name = "Calibri"
    .VerticalAlignment = xlCenter
End With

wks.Range(wks.Cells(5, colNo), wks.Cells(ctr, colNo)).Select

Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
    .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
    .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = 0.13012579
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
    xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
    .ThemeColor = xlThemeColorAccent6
    .TintAndShade = 0.13012579
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
    .Color = 0
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
    .Color = 255
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
    .Color = 255
    .TintAndShade = 0
End With

End Function

Community
  • 1
  • 1
user3150378
  • 335
  • 1
  • 5
  • 14
  • 1) [Avoid Using Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for starters. 2) You do not have a function. You have a sub. Functions return values (of some sort). 3) I suspect the problem may be in the code that calls this function, for each cell, but hard to say for sure, for obvious reasons. – Scott Holtzman Jul 05 '16 at 17:02
  • The calling function formats various columns and then calls this one. Call formatDatabar(wbkO, wks, 35) – user3150378 Jul 05 '16 at 17:25
  • And I changed select to fully qualified range reference and it still does not work. I have to use range reference rather than a loop for each cell to have relative size of the databar. The calling method also clearformats all formatting prior to starting over. In any case, I do not think there should be an issue with residual formatting, since the sheet is deleted and recreated each time and data populated from back-end. – user3150378 Jul 05 '16 at 17:37

1 Answers1

0

In the 2472nd row, the % was very high, making all others almost nothing. This caused the databar for all other rows to not display.

Thanks Scott for your help.

user3150378
  • 335
  • 1
  • 5
  • 14