VBA Conditional Formatting Borders on Worksheet Change Event

emirmansouri

New member
Joined
Nov 17, 2013
Messages
1
Reaction score
0
Points
0
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.

Example

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

mrexcel.com/forum/excel-questions/739852-visual-basic-applications-conditional-formatting-borders-worksheet-change-event.html


 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,199
Reaction score
16
Points
38
Excel Version(s)
365
Try adding the code in red:
Code:
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

[COLOR=#0000ff]<snip>[/COLOR]

        cell.Font.Bold = True
        cell.HorizontalAlignment = xlCenter
      Case 0
        cell.Interior.ColorIndex = 0
        cell.HorizontalAlignment = xlCenter
    End Select
[COLOR=#ff0000]    If cell.Column = 1 And Len(cell.Value) > 0 Then
      With cell.Resize(, 65)
        .BorderAround xlContinuous, xlThin, xlAutomatic
        .HorizontalAlignment = xlCenter
      End With
    End If[/COLOR]
  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.
 
Top