如何将纯色应用于Excel VBA中的数据栏？ [英] How to apply solid colors to data bar in Excel VBA?

问题描述

执行VBA命令时，我的数据栏看起来不错（实线）。但是，在我保存文件并重新打开后，数据栏会自动更改为渐变。

在保存和重新打开文件之前， /i.stack.imgur.com/Z9psG.jpgalt =保存文件之前>

保存并重新打开文件后：

这里是我使用的代码：

  Dim DB As Databar 
设置DB = Range（K2：K10 ）.FormatConditions.AddDatabar 
 
使用DB 
 .BarFillType = xlDataBarSolid 
 .BarBorder.Type = xlDataBarBorderSolid 
使用.BarBorder.Color 
 .Color = 15698432 
结束于
与.BarColor 
 .Color = 15698432 
 .TintAndShade = 0 
结束于
结束于
 
使用DB.BarColor 
 .Color = 15698432 
 .TintAndShade = 0 
结束于
 
使用范围（K2：K10）FormatConditions（1）
 .MinPoint.Modify newtype：= xlConditionValueAutomaticMin 
 .MaxPoint.Modify newtype：= xlConditionValueAutomaticMax 
以
结束 

解决方案

首先，你有两次;

  ... 
使用.BarColor 
 .Color = 15698432 
。TintAndShade = 0 
结束于
结束于
 
使用DB.BarColor 
 .Color = 15698432 
 .TintAndShade = 0 
结束使用
 ... 
  

此外，这是非常重要的：你已经区分了一个数据条 - 你完成填充，它不会改变。如果需要，您可以删除数据栏并重新设置：

 使用范围（K2：K10）
 For i = .FormatConditions.Count To 1 Step -1 
 .FormatConditions（i）.Delete 
 Next 
'创建一个DataBar对象，就像你一直做的那样
 ... 
结束于
  

希望这对你有用。

My data bar looks good (solid) when executing the VBA command. However, after I saved the file and reopened, data bar changed to gradient automatically. How do I avoid this?

Before saving and reopening file:

After saving and reopening file:

Here's the code I used:

Dim DB As Databar
Set DB = Range("K2:K10").FormatConditions.AddDatabar

With DB
    .BarFillType = xlDataBarSolid
    .BarBorder.Type = xlDataBarBorderSolid
    With .BarBorder.Color
        .Color = 15698432
    End With
    With .BarColor
        .Color = 15698432
        .TintAndShade = 0
    End With
End With

With DB.BarColor
    .Color = 15698432
    .TintAndShade = 0
End With

With Range("K2:K10").FormatConditions(1)
    .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
    .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With

解决方案

First of all, you have this twice; either one will suffice.

...
    With .BarColor
        .Color = 15698432
        .TintAndShade = 0
    End With
End With

With DB.BarColor
    .Color = 15698432
    .TintAndShade = 0
End With
...

Also, and this is VERY IMPORTANT: In my experience, once you've difined a Databar--you are done with the fill, and it will not change. If needed, you can delete the Databar and set it up again:

With Range("K2:K10")
    For i = .FormatConditions.Count To 1 Step -1
            .FormatConditions(i).Delete
    Next
    'Create a DataBar object ' as you've been doing it already
    ...
End With

Hope this works for you.

这篇关于如何将纯色应用于Excel VBA中的数据栏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！