如何根据数值条件更改工作簿中所有含数字单元格的数字格式？

我碰到个特殊问题，下面的代码跑不起来，想找到能用的语法，同时得搞清楚代码该放哪儿——要用什么事件？代码层级选Module、ThisWorkbook还是Sheet？另外，能不能用Excel条件格式实现这个需求？

原始代码 #

If Cells.Value = 0 Then
    Cells.NumberFormat = "0"
ElseIf Abs(Cells.Value) > 0 And Abs(Cells.Value) < 0.1 Then
    Cells.NumberFormat = "0.000"
ElseIf Abs(Cells.Value) >= 0.1 And Abs(Cells.Value) < 1 Then
    Cells.NumberFormat = "0.00"
ElseIf Abs(Cells.Value) >= 1 And Abs(Cells.Value) < 10 Then
    Cells.NumberFormat = "0.0"
ElseIf Abs(Cells.Value) >= 10 Then
    Cells.NumberFormat = "0"
End If

已修改的代码（支持货币和百分比格式） #

Sub FormatCellWithNumber(ByVal cell As Range, ByRef FormattedCellsCount As Long)
    Dim Value As Variant
    Dim NumFormat As String
    Value = cell.Value
    If VarType(Value) = vbCurrency Then  ' 识别货币类型
        Select Case Abs(Value)
            Case 0, Is >= 0.1: NumFormat = "$0.00"
            Case Is < 0.1: NumFormat = "$0.000"
        End Select
        FormattedCellsCount = FormattedCellsCount + 1
        cell.NumberFormat = NumFormat
    ElseIf VarType(Value) = vbDouble Then ' 识别数值类型
        If Is_formatted_as_percent(Range(cell.Address)) Then ' 判断是否为百分比格式
            Select Case Abs(Value)
                Case 0, Is >= 0.1: NumFormat = "0.0%"
                Case Is < 0.1: NumFormat = "0.00%"
            End Select
            FormattedCellsCount = FormattedCellsCount + 1
            cell.NumberFormat = NumFormat
        Else ' 普通数值
            Select Case Abs(Value)
                Case 0, Is >= 10: NumFormat = "0"
                Case Is < 0.1: NumFormat = "0.000"
                Case Is < 1: NumFormat = "0.00"
                Case Is < 10: NumFormat = "0.0"
            End Select
            FormattedCellsCount = FormattedCellsCount + 1
            cell.NumberFormat = NumFormat
        End If
    End If
End Sub

Function Is_formatted_as_percent(rng As Range) As Boolean
    Is_formatted_as_percent = rng.NumberFormatLocal Like "*%*"
End Function

条件格式遇到的问题 #

一、VBA代码修复与放置说明 #

1. 原始代码的问题 #

原始代码直接用Cells.Value会报错，因为Cells指的是整个工作表的所有单元格，不能直接取单个值，必须指定具体单元格或者遍历目标范围。

2. 代码放哪儿？ #

• 标准模块（Module）：把修改后的FormatCellWithNumber过程和Is_formatted_as_percent函数丢这儿，方便随时调用。
• 触发事件怎么选？
  • 要是想单元格内容一变就自动格式化，就在目标工作表的Worksheet_Change事件里调用这个过程，示例代码：

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cell As Range
        Dim count As Long
        ' 只处理指定范围，比如A到C列，可自行修改
        For Each cell In Intersect(Target, Me.Range("A:C"))
            If IsNumeric(cell.Value) Then
                FormatCellWithNumber cell, count
            End If
        Next cell
    End Sub
    

    这段代码要放在对应的工作表代码窗口（比如Sheet1的代码窗口）。
  • 要是想批量格式化已有数据，就在标准模块里写个批量执行的宏：

    Sub BatchFormatCells()
        Dim rng As Range
        Dim cell As Range
        Dim count As Long
        ' 指定要格式化的工作表和范围，自行修改
        Set rng = ThisWorkbook.Sheets("Sheet1").Range("A:C")
        For Each cell In rng
            If IsNumeric(cell.Value) Then
                FormatCellWithNumber cell, count
            End If
        Next cell
        MsgBox "总共格式化了" & count & "个单元格"
    End Sub
    

    直接运行这个宏就能批量处理。

3. 代码层级区别 #

• Module：存通用的工具类过程和函数，比如批量格式化的宏、FormatCellWithNumber这种复用性高的代码。
• Sheet代码窗口：存和特定工作表绑定的事件，比如Worksheet_Change，负责该表的单元格变更响应。
• ThisWorkbook：存工作簿级别的事件，比如打开工作簿时自动执行，这里用不上。

二、条件格式实现方法 #

条件格式完全能实现这个需求，但规则顺序非常重要（从上到下匹配，匹配到就停止应用），步骤如下：

1. 选中需要格式化的单元格范围。
2. 点「开始」选项卡→「条件格式」→「新建规则」→选「使用公式确定要设置格式的单元格」。
3. 按以下顺序添加规则（顺序不能乱，先加范围更窄的规则）：
   • 规则1：公式=AND(ABS(A1)>0,ABS(A1)<0.1)，设置数字格式为0.000。
   • 规则2：公式=AND(ABS(A1)>=0.1,ABS(A1)<1)，设置数字格式为0.00。
   • 规则3：公式=AND(ABS(A1)>=1,ABS(A1)<10)，设置数字格式为0.0。
   • 规则4：公式=OR(ABS(A1)=0,ABS(A1)>=10)，设置数字格式为0。
4. 货币和百分比格式的额外规则：
   • 货币：公式=AND(VAR(A1)=vbCurrency,ABS(A1)<0.1)，格式设为$0.000；另一条公式=AND(VAR(A1)=vbCurrency,OR(ABS(A1)=0,ABS(A1)>=0.1))，格式设为$0.00。
   • 百分比：公式=AND(CELL("format",A1)="P",ABS(A1)<0.1)，格式设为0.00%；另一条公式=AND(CELL("format",A1)="P",OR(ABS(A1)=0,ABS(A1)>=0.1))，格式设为0.0%。

你的条件格式问题分析 #

从截图看，大概率是规则顺序错了，或者公式写得不对。比如要把范围更小的（比如<0.1）规则放在前面，因为条件格式是从上到下匹配，先匹配到的会优先应用格式，要是把大范围的规则放前面，小范围的就不会触发了。

内容的提问来源于stack exchange，提问作者KiloOne