Help with VB code please?

[tonyd789]

Hi
What im after is a code to place within a macro that i've wrote that will place borders around every cell within a row.

Basically, if A2 has a value then A2 all the way to Z2 will have a borders around each cell. If A3 has nothing it wont put borders round the row, but A4 would have a value and it would do borders again and so on.

The gaps within the spread sheet change each time the macro runs.
I would only want to perform this action for about 30 rows down and then stop as there is other info after A30, and i dont want borders going to Z30.
Conditonal formatting is not an option as it leaves gap where there is no values in cells.

I hope that makes sence and many thanks for any help given.

[mdmackillop]

Try conditional formatting using this formula =COUNTA($A2:A2)>0

[tonyd789]

Ok so maybe conditonal formatting is an option hahaha

Many Thanks and thanks for the quick responce

Tony

[tonyd789]

Ok so after playing with the formula it turns out that it doesn't quite work the way i want it to after the macro has finished doing what it does.

I'm sure there will be a VB code that will do it for me, and i can just slot it in at the end of the macro, but i havent got a clue on where to start with writing that. So back to squre 1.

Help please?

Tony

[mdmackillop]

Can you post a sample file with explanatory comments?

[tonyd789]

One of the issues is that my spreadsheet has formulas scattered around it and the conditional formating places borders around that and to the end of the range.

The macro when it runs cuts and pastes columns here there and everywhere and also creates new one. Im thinking VB code is probably the easist way as i could just place it at the end of the code already there. Problem is i don't know what to write.

[mdmackillop]

If the conditional formatting works in principle, then record a macro to delete the existing and reapply the conditional formatting to the required area.

[tonyd789]

Thats the thing, the areas change each time the macro finishes. The data it sorts out is different each time.

[mdmackillop]

See post #5

[tonyd789]

So after racking my brain and thinking hard about what you gave me this is what i came up with that works.

Range("A1:Y35").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNT($B1:b1)>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False

Thanks for the help