Excel 2010 VBA to format cells

Hi Experts,

I've written a simple code based on macro recordings to create a border around a range of cells if the active cell is not blank and is within the row range of 4-50.  There are no bugs found in the code but it doesn't work.  Any help is appreciated, thank you!
000-Project-Estimator.xlsm
Skip SleeperIT ManagerAsked:
Who is Participating?
 
SteveConnect With a Mentor Commented:
Is there a reason why you do not use Conditional Formatting?

Do you want the code to 'fire' automatically?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'
' FormatCells Macro
'

'
    
  If Target.Column = 1 Then
            If Target.Row >= 4 And Target.Row <= 50 And _
            IsEmpty(Target.Value) Then
            End
            Else
            With Target
                
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ThemeColor = 1
                    .TintAndShade = -0.349986266670736
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ThemeColor = 1
                    .TintAndShade = -0.349986266670736
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ThemeColor = 1
                    .TintAndShade = -0.349986266670736
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ThemeColor = 1
                    .TintAndShade = -0.349986266670736
                    .Weight = xlThin
                End With
                With .Borders(xlInsideVertical)
                    .LineStyle = xlContinuous
                    .ThemeColor = 1
                    .TintAndShade = -0.349986266670736
                    .Weight = xlThin
                End With
            End With
            End If
            
    End If
End Sub

Open in new window

0
 
gowflowCommented:
Is this what you want ??

type anything in  a cell form row 4 to row 50 in Column 1 and then press on macro and select it, it will give you what you designed a very light border.

gowflow
000-Project-Estimator.xlsm
0
 
gowflowCommented:
No problem with your code Barman
gowflow
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
MacroShadowCommented:
@fargus47

Your code works fine for me.
0
 
Skip SleeperIT ManagerAuthor Commented:
Hi,

I thought the code would "fire" automatically and not need a command button.  What would the correct code be to do that?  Also, I didn't know conditional formatting would just do borders.  

Thanks!
0
 
MacroShadowCommented:
That's the code Barman gave you.
0
 
DOSLoverCommented:
It works for me. I don't see any problem with the code. May be the new border is not visible bcz it is lighter. I also tried including the following code, it shows up new color!
Selection.Font.ColorIndex = 3
0
 
SteveCommented:
Conditional formatting can do just borders and is far better than code as it will "undo" automatically too.

The code I did at the start is for the auto fire macro too.

If either need explaining then let us know, we are happy to help.
0
 
Skip SleeperIT ManagerAuthor Commented:
It works great, thanks!  Why didn't my code work?  I've not used "Target" before.  Also once i looked at bit more at conditional formatting it makes much more sense to use that.

Thanks!
0
 
SteveCommented:
You need to pick the right workshhet event for excel to work automatically. Once you get that, objects like Target are set automatic too.
If you research worksheet events you will see that there are a few to choose from.
0
 
Skip SleeperIT ManagerAuthor Commented:
OK, thanks for everything!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.