vba 在具有相同值的单元格周围创建边框

Creating a border around cells with the same value

提问by Liam

I have a table like the one below. How can I get Excel to put borders around groups with the same number in the 4th column so that there is a border around the groups. I was thinking conditional formatting could do it but I can't think how. So I think the only option is a macro. Could anybody help?

我有一张像下面这样的桌子。如何让 Excel 在第 4 列中为具有相同编号的组设置边框，以便在组周围有边框。我在想条件格式可以做到，但我不知道怎么做。所以我认为唯一的选择是宏。有人可以帮忙吗？

 1 64436 549419 1  
 2 64437 549420 1  
 3 64438 549421 1  
 4 64439 549422 1  
 5 64440 549423 1  
 6 64441 549424 1  
 7 64442 549425 1  
 8 64443 549426 1  
 9 64444 549427 1  
 10 64445 549428 1  
 11 64446 549429 1  
 12 64447 549430 1  
 13 64448 549431 2  
 14 64449 549432 2  
 15 64450 549433 2  
 16 64451 549434 2  
 17 64452 549435 2  
 18 64453 549436 2  
 19 64454 549437 2  
 20 64455 549438 2  
 21 64456 549439 2  
 22 64457 549440 4  
 23 64458 549441 4  
 24 64459 549442 5  
 25 64460 549443 5  
 26 64461 549444 5  
 27 64462 549445 5  
 28 64463 549446 5  
 29 64464 549447 5  
 30 64465 549448 6  
 31 64466 549449 6  
 32 64467 549450 6  
 33 64468 549451 6  
 34 64469 549452 6  
 35 64470 549453 6  
 36 64471 549454 6  
 37 64472 549455 9  
 38 64473 549456 9  
 39 64474 549457 9  

回答by David

You need to use relative referencing.

您需要使用相对引用。

1. Select the column range you want to do the conditional formatting on.
2. Enter the following three formulas in their own conditions:
   • =AND($C2=$C3,$C3=$C4)
     • This one is for the middle items. (Borders on both sides)
   • =AND($C2<>$C3,$C3=$C4)
     • This one is for the first in the group. (Border on left, top, right)
   • =AND($C2=$C3,$C3<>$C4)
     • This one is for the last in the group. (Border on left, bottom, right)
3. Format them as you want.

1. 选择要对其进行条件格式设置的列范围。
2. 在自己的条件下输入以下三个公式：
   • =AND($C2=$C3,$C3=$C4)
     • 这是中间件。（两边的边界）
   • =AND($C2<>$C3,$C3=$C4)
     • 这是该组中的第一个。（左、上、右边框）
   • =AND($C2=$C3,$C3<>$C4)
     • 这是小组中的最后一个。（左、下、右边框）
3. 根据需要格式化它们。

Replace all '$C' with '${Your Column}'. Note that this will not place any borders around single items since you can have no more the three conditional formatting conditions in a selection.

将所有 '$C' 替换为 '${Your Column}'。请注意，这不会在单个项目周围放置任何边框，因为您在选择中不能再有三个条件格式条件。

回答by Alex P

I cannot see a simple non-macro solution to exactly what you need but the solution from PowerUser seems okay.

我看不到一个简单的非宏解决方案来满足您的需求，但 PowerUser 的解决方案似乎没问题。

Here is a macro based solution that will put a border around rows that have the same digit in the final column. I will assume your data are in columns A:D.

这是一个基于宏的解决方案，它将在最后一列中具有相同数字的行周围放置一个边框。我将假设您的数据在 A:D 列中。

To use this macro just click any cell within your list and then fire the macro.

要使用此宏，只需单击列表中的任何单元格，然后启动该宏。

As a quick guide:

作为快速指南：

• AddBordersis the main macro that simply loops through all the cells in the final column and works out when a border is appropriate
• AddBorderis a short routine that adds the border.

• As a bonus, AddBorderselects a random color from Excel's 56 color palette so that each of your borders are different colors to make easier viewing

  Sub AddBorders()
      Dim startRow As Integer
      Dim iRow As Integer
      startRow = 1    
      For iRow = 2 To ActiveCell.CurrentRegion.Rows.Count    
          If WorksheetFunction.IsNumber(Cells(iRow + 1, 4)) Then
              If Cells(iRow, 4) <> Cells(iRow - 1, 4) Then
                 AddBorder startRow, iRow - 1
                 startRow = iRow
              End If
          Else
                 AddBorder startRow, iRow
          End If   
      Next iRow    
  End Sub
  
  Sub AddBorder(startRow As Integer, endRow As Integer)
      Dim borderRange As Range
      Dim randomColor As Integer
      randomColor = Int((56 * Rnd) + 1)
      Set borderRange = Range("A" & startRow & ":D" & endRow)
      borderRange.BorderAround ColorIndex:=randomColor, Weight:=xlThick    
  End Sub
  

• AddBorders是主要的宏，它简单地遍历最后一列中的所有单元格，并在边框合适时计算出
• AddBorder是添加边框的简短例程。

• 作为奖励，AddBorder从 Excel 的 56 种调色板中随机选择一种颜色，以便您的每个边框都是不同的颜色，以便于查看

  Sub AddBorders()
      Dim startRow As Integer
      Dim iRow As Integer
      startRow = 1    
      For iRow = 2 To ActiveCell.CurrentRegion.Rows.Count    
          If WorksheetFunction.IsNumber(Cells(iRow + 1, 4)) Then
              If Cells(iRow, 4) <> Cells(iRow - 1, 4) Then
                 AddBorder startRow, iRow - 1
                 startRow = iRow
              End If
          Else
                 AddBorder startRow, iRow
          End If   
      Next iRow    
  End Sub
  
  Sub AddBorder(startRow As Integer, endRow As Integer)
      Dim borderRange As Range
      Dim randomColor As Integer
      randomColor = Int((56 * Rnd) + 1)
      Set borderRange = Range("A" & startRow & ":D" & endRow)
      borderRange.BorderAround ColorIndex:=randomColor, Weight:=xlThick    
  End Sub
  

回答by Marek

I came out with this solution, it works strange on my Excel 2010 :/ I cannot test it on 2003, so please let me know if thats fine.

我提出了这个解决方案，它在我的 Excel 2010 上工作很奇怪：/我无法在 2003 上测试它，所以请告诉我是否可以。

Sub PaintBorder()
Dim iRow As Integer
iRow = 1
Dim strTemp As String
strTemp = Range("D" & iRow).Value
Dim strPrev As String

Dim sectionStart As Integer
sectionStart = 1

Do
    strPrev = strTemp
    strTemp = Range("D" & iRow).Value

    If strPrev <> strTemp Then
        ActiveSheet.Range(Cells(sectionStart, 1), Cells(iRow - 1, 4)).BorderAround xlSolid, xlMedium, xlColorIndexAutomatic
        sectionStart = iRow
    End If
    iRow = iRow + 1
Loop Until strTemp = vbNullString
End Sub

回答by PowerUser

Are you just trying to make it more readable to human eyes? If so, I recommend alternating background colors. For example, every time, the number in that 4th column changes, the background color would change from white to blue and vice-versa. I do this all the time:

你只是想让它对人眼更具可读性吗？如果是这样，我建议交替使用背景颜色。例如，每次第 4 列中的数字发生变化时，背景颜色都会从白色变为蓝色，反之亦然。我一直这样做：

1. Make an additional column E. Since your reference column is D, enter:
   =MOD(IF(D5<>D4,E4+1,E4),2)
   (i.e. if this row's column D is different from the last row's D, then change from either 0 to 1 or 1 to 0)

2. Hide the column so that the end-user doesn't see it.

3. Make 2 conditional formulas. The first will change the row color to white if your hidden value is 0. The second will change it to blue if your hidden value is 1.

1. 制作额外的列 E。由于您的参考列是 D，因此输入：
   =MOD(IF(D5<>D4,E4+1,E4),2)
   （即，如果该行的 D 列与最后一行的 D 不同，则从 0 变为 1 或 1 变为 0）

2. 隐藏该列，以便最终用户看不到它。

3. 做2个条件公式。如果您的隐藏值为 0，第一个会将行颜色更改为白色。如果您的隐藏值为 1，则第二个会将其更改为蓝色。

No macros. No VBA coding. Just 1 hidden column and a few conditional formulas. And the colors should still alternate properly even though your column D is skipping numbers :)

没有宏。没有 VBA 编码。只有 1 个隐藏列和一些条件公式。即使您的 D 列跳过数字，颜色仍应正确交替:)

(I use this daily on XL 2003. I hope it works on 2007)

（我每天在 XL 2003 上使用它。我希望它适用于 2007）