循环浏览选定的单元格，检查边框并更改颜色 [英] Loop through selected cells, check for borders and change colour

问题描述

我正在为工具栏创建一个按钮，用于将选定区域内的颜色从白色背景，黑色字体更改为白色字体黑色背景.(将用于会计中的损益表，资产负债表等表格).

但是我还需要按钮中的某些功能，这些功能可以浏览选定的单元格，找到任何现有的边框并将其变为白色.通过布尔值或其他方法检查背景颜色是否为黑色，然后将现有的边框变成白色来进行练习.我不需要做任何新的边框，只需反转现有边框的颜色即可.

这是我已经拥有的，但是它只会使所有边框变成白色:

 将背景调暗为布尔值暗淡范围选择昏暗范围作为范围设置selectedRange = Application.Selection带有Selection.Borders对于selectedRange.Cells中的每个cel如果cel.Borders(xlEdgeTop).LineStyle<>xlLineStyleNone然后颜色= RGB(255，255，255)万一如果cel.Borders(xlEdgeBottom).LineStyle<>xlLineStyleNone然后颜色= RGB(255，255，255)万一下一个结束于 

希望你能够帮助我:)

解决方案

您正在将颜色应用于 Selection.Borders 集合中的每个边框，因为这是 With 变量.只需设置 cel.Borders(xlEdgeTop)

的颜色

  Dim cel作为范围选择昏暗范围作为范围设置selectedRange = Application.Selection对于selectedRange.Cells中的每个cel与celBorders如果.Item(xlEdgeTop).LineStyle<>xlLineStyleNone然后.Item(xlEdgeTop).Color = vbWhite万一如果.Item(xlEdgeBottom).LineStyle<>xlLineStyleNone然后.Item(xlEdgeBottom).Color = vbWhite万一结束于下一个 

您还可以使用两个 With 块: With cel.Borders(xlEdgeTop)和 With cel.Borders(xlEdgeBottom)使用 .LineStyle 和 .Color .您也可以完全跳过 With 块，因为它实际上在这里并没有节省太多( cel.Borders -> .Item ).

I am creating a button for a toolbar to change colors from white background, black font, to white font black background within a selected area. (will be used for tables like P&L's, balance sheets etc in accounting).

But I also need some functionality in the button that looks through the cells that are selected, locates any existing borders and turns them white. Perhabs by having a boolean or something checking if the background color is black, and then turning existing borders white. I does not need to make any new borders, only invert the colors of existing ones.

This is what i've already have, but it just makes all borders white:

Dim Background As Boolean
Dim cel As Range
Dim selectedRange As Range

Set selectedRange = Application.Selection

With Selection.Borders

    For Each cel In selectedRange.Cells
    If cel.Borders(xlEdgeTop).LineStyle <> xlLineStyleNone Then
            .Color = RGB(255, 255, 255)
        End If

    If cel.Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
            .Color = RGB(255, 255, 255)
        End If

    Next cel
End With

Hope you're able to help me out :)

解决方案

You are applying the color to every border in the Selection.Borders collection because that's the With variable. Just set the color of cel.Borders(xlEdgeTop)

Dim cel As Range
Dim selectedRange As Range

Set selectedRange = Application.Selection
For Each cel In selectedRange.Cells
    With cel.Borders
        If .Item(xlEdgeTop).LineStyle <> xlLineStyleNone Then
            .Item(xlEdgeTop).Color = vbWhite
        End If

        If .Item(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
            .Item(xlEdgeBottom).Color = vbWhite
        End If
    End With
Next cel

You could also use two With blocks: With cel.Borders(xlEdgeTop) and With cel.Borders(xlEdgeBottom) and then just use .LineStyle and .Color. You could also skip the With block altogether because it really doesn't save much here (cel.Borders -> .Item).

这篇关于循环浏览选定的单元格，检查边框并更改颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！