如何避免Excel宏设置单元格背景时出现黑色及近黑色

你当前代码里用 Int((56 * Rnd) + 1) 生成的ColorIndex范围是1到56，其中索引1对应黑色，还有部分低索引值是深色调，这就是问题根源。下面提供几种可靠的解决思路：

方案1：预定义安全的ColorIndex列表 #

直接从Excel的ColorIndex中筛选出浅色系的索引值，随机从中选取，彻底避开深色：

With ActiveSheet
    rg.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Font.Size = 18
        .ColumnWidth = 30
        .RowHeight = 80
        
        ' 预定义所有浅色系的ColorIndex值
        Dim safeColorIndexes As Variant
        safeColorIndexes = Array(9, 10, 11, 12, 13, 14, 15, 16, _
                                20, 21, 22, 23, 24, 25, 26, 27, _
                                28, 29, 30, 31, 32, 33, 34, 35, _
                                36, 37, 38, 39, 40, 41, 42, 43, _
                                44, 45, 46, 47, 48, 49, 50, 51, _
                                52, 53, 54, 55, 56)
        ' 从安全列表中随机选一个颜色
        .Interior.ColorIndex = safeColorIndexes(Int(Rnd * UBound(safeColorIndexes) + 1))
    End With
End With

方案2：用RGB生成亮色背景 #

通过控制RGB三个通道的最小值，确保生成的颜色足够明亮，不会出现深色：

With ActiveSheet
    rg.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Font.Size = 18
        .ColumnWidth = 30
        .RowHeight = 80
        
        ' 生成RGB值，每个通道范围100-255，保证亮度足够
        Dim r As Integer, g As Integer, b As Integer
        r = Int(Rnd * 156) + 100
        g = Int(Rnd * 156) + 100
        b = Int(Rnd * 156) + 100
        .Interior.Color = RGB(r, g, b)
    End With
End With

方案3：动态判断颜色亮度（更灵活） #

如果想要更大的颜色范围，同时确保背景亮度足够，可以通过计算颜色的相对亮度来筛选，当亮度低于阈值时重新生成：

With ActiveSheet
    rg.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Font.Size = 18
        .ColumnWidth = 30
        .RowHeight = 80
        
        Dim r As Integer, g As Integer, b As Integer
        Dim brightness As Double
        Do
            ' 生成全范围RGB值
            r = Int(Rnd * 256)
            g = Int(Rnd * 256)
            b = Int(Rnd * 256)
            ' 计算相对亮度（标准公式）
            brightness = (0.299 * r) + (0.587 * g) + (0.114 * b)
        Loop While brightness < 120 ' 亮度低于120则重新生成
        
        .Interior.Color = RGB(r, g, b)
    End With
End With

以上三种方案都能有效避免黑色及近黑色背景，你可以根据自己对颜色多样性的需求选择。

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