Results 1 to 2 of 2

Thread: VBA Conditional Formatting Borders on Worksheet Change Event

  1. #1

    Post VBA Conditional Formatting Borders on Worksheet Change Event

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I would like to add to my existing worksheet change event a code that will format a Range (A9:BM50000)
    if cell in Column A is not blank then apply a normal thin border around that row and center the contents.


    A9 is not blank, apply the border from A9:BM9 & center the contents of the cells
    if it is blank do nothing.

    If someone could get this integrated with my code it would be greatly appreciated.

    Here is a link to my thread on MrExcel

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Excel Version
    Try adding the code in red:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim rInt As Range
    Dim rng As Range
    Set rInt = Intersect(Target, Range("A9:BM50000"))
    If Not rInt Is Nothing Then
      For Each cell In rInt
        Select Case cell.Value
          Case "X"
            cell.Interior.ColorIndex = 3
            cell.Font.Bold = True
            cell.HorizontalAlignment = xlCenter
          Case 0
            cell.Interior.ColorIndex = 0
            cell.HorizontalAlignment = xlCenter
        End Select
        If cell.Column = 1 And Len(cell.Value) > 0 Then
          With cell.Resize(, 65)
            .BorderAround xlContinuous, xlThin, xlAutomatic
            .HorizontalAlignment = xlCenter
          End With
        End If
      Next cell
    End If
    If Not Intersect(Target, Range("D9:D50000")) Is Nothing Then
      Application.EnableEvents = False
      Colorize Intersect(Target, Range("D9:D50000"))
      Application.EnableEvents = True
    End If
    End Sub
    Note that I have paid attention to your "if it is blank do nothing", which means that if a cell in column A which once had something in it and had caused the row to be formatted, becomes blank, then that row's formatting will remain unchanged.
    Also, this doesn't strictly go through the whole A9:A50000, only Intersect(Target, Range("A9:BM50000")) but it will format the whole row (A:BM) if that range includes non-empty cells in column A; post back if this is wrong.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts