为什么在智能表格中未设置正确的字体颜色? [英] Why isn't the right font color set in the smart table?

问题描述

我在纸上使用表格. 我选中隐藏价格"框并运行相关代码.代码将目标单元格中​​的值隐藏起来，将它们中的字体颜色设置为填充颜色. 当您再次单击复选标记时，代码将再次启动，并在单元格中将字体颜色设置为与左侧示例性单元格相同. 由于使用表格，因此用户可以选择某种快速样式，其中单元格中的字体颜色可以不同.因此，我不能设置任何特定的颜色，例如黑色.

I use Tables on a sheet. I check the box "Hide price" and run the associated code. The code hides the values ​​in the target cells, setting the font color in them to the fill color. When you click the check mark again, the code starts again and sets the font color in the cell the same as in the exemplary cell on the left. Since Tables are used, the user can choose some express style in which the font color in the cells can be different. Therefore, I can not set any particular color, for example, black.

With Sheets("Calculation")
    For r = 9 To 10
    For c = 22 To 23
        .Cells(r, c).Select
        clr1 = .Cells(r, c).DisplayFormat.Interior.ColorIndex
        clr2 = .Cells(r, c).DisplayFormat.Font.ColorIndex
        'colorindex of exemplary cell
        .Cells(r, 21).Select
        clr3 = .Cells(r, 20).DisplayFormat.Font.ColorIndex
        v = .Cells(2, 25).Value
        If .Cells(2, 25).Value = True Then
            If clr1 > 0 Then
                .Cells(r, c).Font.ColorIndex = clr1
            Else
                .Cells(r, c).Font.Color = 16777215
            End If
        Else
            .Cells(r, c).Font.ColorIndex = clr3
        End If
    Next c
    Next r

End With

由于某种原因，当我恢复字体颜色时，我设置了不正确的颜色. 如果您查看示例性单元格中字体的颜色，请执行以下操作: 单元格样式/创建单元格样式，然后颜色:Text1. 如果您查看目标单元格中​​的字体颜色: 单元格样式/创建单元格样式，然后设置颜色:米色(rgb 128,128,0).

For some reason, when I restore the font color, I set an incorrect color. If you look at the color of the font in the exemplary cell: Cell styles / Create cell style, then color: Text1. If you look at the font color in the target cell: Cell styles / Create cell style, then color: Beige (rgb 128,128,0).

我在做什么错了?

已更新.工作代码

Dim oSh As Worksheet
Dim rNg As Range
Set oSh = Sheets("Calculation")
Set rNg = oSh.Range("T_1_1[[Column22]:[Column23]]")
With oSh
    'bring in a variable cell format
    cLr = oSh.ListObjects("T_1_1").ListColumns(20).DataBodyRange.NumberFormat
    If .Cells(2, 25).Value = True Then
        'set "zero" formatting
        rNg.NumberFormat = ";;;"
    Else
        'apply formatting from sample
        rNg.NumberFormat = cLr
    End If
End With

推荐答案

如何通过NumberFormat ;;;

要隐藏单元格的内容，建议不要更改颜色.
而是存储原始的Range.NumberFormat并将其设置为Range.NumberFormat = ";;;"，这将隐藏正值，负值，空值和文本i. e.它会隐藏除错误以外的所有内容.

To hide a cell's content, I suggest not to change colors.
Instead store the original Range.NumberFormat and set it to Range.NumberFormat = ";;;" which hides positive values, negative values, null values and text, i. e. it hides everything except errors.

常规颜色和ColorIndex

您始终可以读取或写入单元格的标准颜色，例如. g.

You can always read or write the standard color of a cell, e. g.

• Font.Color
• Interior.Color

• Font.Color
• Interior.Color

颜色值是3个字节的RGB颜色，可以由RGB(red byte, green byte, blue byte)设置并存储在Long变量中(4个字节，表示0BGR颜色字节).

The color value is a 3 byte RGB color, can be set by RGB(red byte, green byte, blue byte) and be be stored in Long variables (4 bytes, representing 0BGR color bytes).

ColorIndex的值只能在1到56之间-结果会有所不同，具体取决于工作簿的调色板.

The ColorIndex is a value between 1 and 56 only - with varying results, dependig on the color palette of the workbook.

按DisplayFormat显示的颜色

可以通过条件格式或表格样式来更改显示的颜色.它不会更改标准颜色，而是将其覆盖或可见，而不是基础标准颜色.
您只能读取重叠颜色，但不能不直接为每个单元格设置颜色:

The displayed color can be changed by conditional formatting or a table style. It does not change the standard color, but overlays it or is visible instead of the underlying standard color.
You can read the overlaying color only, but you can not set it directly for each cell:

• DisplayFormat.Font.Color
• DisplayFormat.Interior.Color

• DisplayFormat.Font.Color
• DisplayFormat.Interior.Color

如果更改基础.Font.Color，则可见结果取决于重叠DisplayFormat的模式.

If you change the underlying .Font.Color, the visible result depends on the pattern of the overlaying DisplayFormat.

按条件格式设置的DisplayFormat(FormatConditions)

如果要更改条件格式的DisplayFormat颜色，则不能直接在单元格区域本身上而是在该范围的条件格式内进行设置.尝试一下:

If you want to change the DisplayFormat color of a conditional formatting, you can not set it directly on the cell's range itself but within the conditional formatting(s) for that range. Experiment with this:

Private Sub DisplayColorByFormatCondition()
    Dim i As Long
    With ActiveSheet.Range("A1")
        Debug.Print "Color Info for " & .Cells.Address
        Debug.Print "Standard Font Color " & .Font.Color & _
                    " is displayed as " & .DisplayFormat.Font.Color; ""
        Debug.Print "Standard Interior Color " & .Interior.Color & _
                    " is displayed as " & .DisplayFormat.Interior.Color
        If .FormatConditions.Count = 0 Then
            Debug.Print "This cell is not part of a FormatCondition."
        Else
            For i = 1 To .FormatConditions.Count
                With .FormatConditions(i)
                    Debug.Print "Condition " & i & " sets Font Color to " & .Font.Color & _
                        "and Interior Color to " & .Interior.Color
                End With
            Next i
        End If
    End With
End Sub

按表格样式(ListObject.TableStyle)的DisplayFormat

如果DisplayFormat颜色是由表格样式产生的，请尝试以下操作:

If the DisplayFormat color results from a table's style, experiment with this:

Private Sub DisplayColorByTableStyle()
    Dim lo As ListObject
    Dim i As Long
    With ActiveSheet.Range("A1")
        Debug.Print "Color Info for " & .Cells.Address
        Debug.Print "Standard Font Color " & .Font.Color & _
                    " is displayed as " & .DisplayFormat.Font.Color; ""
        Debug.Print "Standard Interior Color " & .Interior.Color & _
                    " is displayed as " & .DisplayFormat.Interior.Color
        For Each lo In ActiveSheet.ListObjects
            If Not Intersect(lo.Range, .Cells) Is Nothing Then
                Debug.Print "Cell is part of ListObject '" & lo.Name & _
                            "' which uses TableStyle '" & lo.TableStyle & "'"
                If Not Intersect(lo.HeaderRowRange, .Cells) Is Nothing Then
                    Debug.Print "Cell is part of HeaderRowRange. Font color is set to " & _
                                lo.HeaderRowRange.DisplayFormat.Font.Color & _
                                ", Interior color is set to " & _
                                lo.HeaderRowRange.DisplayFormat.Interior.Color
                ElseIf Not Intersect(lo.DataBodyRange, .Cells) Is Nothing Then
                    Debug.Print "Cell is part of DataBodyRange. Font color is set to " & _
                                lo.DataBodyRange.DisplayFormat.Font.Color & _
                                ", Interior color is set to " & _
                                lo.DataBodyRange.DisplayFormat.Interior.Color
                    For i = 1 To lo.ListRows.Count
                        If Not Intersect(lo.ListRows(i).Range, .Cells) Is Nothing Then
                            Debug.Print "Cell is part of ListRows(" & i & "). " & _
                                        "Font color is set to " & _
                                        lo.ListRows(i).Range.DisplayFormat.Font.Color & _
                                        ", Interior color is set to " & _
                                        lo.ListRows(i).Range.DisplayFormat.Interior.Color
                        End If
                    Next i
                End If
            End If
        Next lo
    End With
End Sub

这篇关于为什么在智能表格中未设置正确的字体颜色?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！