Format Border Based On Cell Content

Pat · Jul 12th 2007

Hi,
I have a dynamic table (rows regularly added or deleted) that is generated automatically based on choices made by the user in another worksheet. I need a function that will examine all the cells in one column, and when it finds a difference between two cell entries, it will change the border between these two entries to dark (medium weight), and then extend this border across the table. The end result should be that wherever the cell contents differ within a column, the table is divided by a darker border across the entire row.
Any advice appreciated.

thanks

holbrookp · Jul 12th 2007

Re: Border Formatting Based On Cell Content

try conditional formatting?

Fencliff · Jul 12th 2007

Re: Border Formatting Based On Cell Content

Here is a macro that will do this from Cell A1 to the last filled cell in column A. If you need this to run constantly, you can use the Worksheet_Change event to monitor the column.

Code

Sub DrawBorders()


    Dim rCell As Range
    Dim rRange As Range
    
    Set rRange = Range("A1", Range("A65536").End(xlUp))
    
    For Each rCell In rRange
        If Not IsEmpty(rCell) And _
           Not IsEmpty(rCell.Offset(1, 0)) Then
            With rCell
                If .Value <> .Offset(1, 0).Value Then
                    With .EntireRow.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                End If
            End With
        End If
    Next rCell


End Sub

Display More

Hope this helps,
Fencliff

Pat · Jul 12th 2007

Re: Border Formatting Based On Cell Content

Thanks, this works perfectly, but how can I modify EntireRow to a specific Range?

Fencliff · Jul 12th 2007

Re: Border Formatting Based On Cell Content

Instead of

Code

With .EntireRow.Borders(xlEdgeBottom)

use

Code

With .Resize(1, 10).Borders(xlEdgeBottom)

Where 10 is the number of columns you want to extend the border to.

Rgds,
Fencliff

Dave Hawley · Jul 13th 2007

Re: Border Formatting Based On Cell Content

What's wrong with Conditional Formatting?

Fencliff · Jul 13th 2007

Re: Format Border Based On Cell Content

Dave, at least in Excel 2003 you can't (oddly enough) apply medium width borders with conditional formatting. It's one of these Excel "features" I've never really understood myself.

Pat · Jul 17th 2007

Re: Format Border Based On Cell Content

Thanks, the Resize coding works great, but I would like to learn how to apply it to the preceeding columns as well. My qualifying column is "E" and I would like the dark border to extend from column "A" to column "BB" in the following:

Code

Private Sub CBPAGrid_Click()
        
    Dim rCell As Range
    Dim rRange As Range
     
    Set rRange = Range("E18", Range("E1000").End(xlUp))
     
    For Each rCell In rRange
        If Not IsEmpty(rCell) And _
        Not IsEmpty(rCell.Offset(1, 0)) Then
            With rCell
                If .Value <> .Offset(1, 0).Value Then
                    With .Resize(1, 50).Borders(xlEdgeBottom)
                        .Weight = xlMedium
                        
                    End With
                End If
            End With
        End If
    Next rCell
        
    End Sub

Display More

Fencliff · Jul 18th 2007

Re: Format Border Based On Cell Content

Hi, only changed one line in the code:

Code

Private Sub CBPAGrid_Click() 
     
    Dim rCell As Range 
    Dim rRange As Range 
     
    Set rRange = Range("E18", Range("E1000").End(xlUp)) 
     
    For Each rCell In rRange 
        If Not IsEmpty(rCell) And _ 
        Not IsEmpty(rCell.Offset(1, 0)) Then 
            With rCell 
                If .Value <> .Offset(1, 0).Value Then 
                    With Range("A" & rCell.Row, "BB" & rCell.Row).Borders(xlEdgeBottom) 
                        .Weight = xlMedium 
                    End With 
                End If 
            End With 
        End If 
    Next rCell 
     
End Sub

Display More

Pat · Jul 18th 2007

Re: Format Border Based On Cell Content

Thanks very much, this works perfectly.

Format Border Based On Cell Content

Participate now!

Share