CODEWITHSUNDEEP
excelvbaconditional-formattingexcel-2016
morg
morg

Reputation: 421

VBA Conditional Formatting Data Bar "Percent/Progress"

Basically, I am trying to do this:

https://i.sstatic.net/AGzz7.jpg

(three pictures)

So I recorded a macro (with intent to clean it up later!) I want the cell data bar to essentially be the size of the percent that is in the cell.

I receive this as the recording. 

Range("H439:H445").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
    .MinPoint.Modify newtype:=xlConditionValueLowestValue
    .MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With Selection.FormatConditions(1).BarColor
    .Color = 49407
    .TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
With Selection.FormatConditions(1).NegativeBarFormat.Color
    .Color = 255
    .TintAndShade = 0
End With

I received this code repeated multiple times from the recording, but upon searching for answers I realize it is an excel bug or problem/error. However I feel that my problem is the xlConditionValueHighestValue (and lowest) part. Another part of the code that was recorded shows xlAutomaticMax/xlAutomaticMin in it's place.

When I run the one part of the code (the posted part), it doesn't fill up based on the Max/Min value I entered (1, 0). Instead it takes the highest number (in this case the 33%) and fills that cell all the way. When I change that part to:

With Selection.FormatConditions(1)
       .MinPoint.Modify newtype:=0
       .MaxPoint.Modify newtype:=1
    End With

I get a "Run-time error '1004': Application-defined or object-defined error" that highlights the 

.MaxPoint.Modify newtype:=1

line, so it likes the 0 line. However I do not know the proper coding procedure to get it to do what I want and I have not found anything on the forums that uses the maxpoint or minpoint formula line, otherwise I would try to copy their notation. Anybody know how to help?

Upvotes: 2

Views: 2810

Answers (1)

pnuts
pnuts

Reputation: 59485

Please try:

 With Selection.FormatConditions(1)
        .MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
        .MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=1
 End With

Alternatively, you might stick with what you have but include a hidden row with a value of 1.

Upvotes: 2

Related Questions