Creating a border around cells with the same value.

Question

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?

 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  

Answer 1

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.

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.

Answer 2

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.

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

Answer 3

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:

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.

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 :)

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

Answer 4

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

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.

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

As a quick guide:

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

• As a bonus, AddBorder selects 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