Cannot find cell borders using vba

**spinner99** · 03-12-2009, 09:05 AM

I'm trying to get a macro working in excel 2007 that works fine in 2003. the macro needs to find all cells with a double line border. The code to do this in 2003 was

Please Login or Register  to view this content.

This worked but in 2007 the returned range is nothing. I have recorded a macro to find with the border I have in 2007 and have made some changes to the macro because some of the cell properties are different when viewed in 2007. My code now looks like

Please Login or Register  to view this content.

This does not return any range. I can though make the find work in 2007 by looking for the fill colour of a cell - but not the borders. Also the macro that I recorded will not work either - ie it will not find the cell even though it works while I am recording it. Any help on this would be gratefully received...

**Andy Pope** · 03-12-2009, 09:42 AM

can you post a sample workbook where your 2003 code will work.

**spinner99** · 03-12-2009, 09:56 AM

please see attached file

**Andy Pope** · 03-12-2009, 11:35 AM

Looks like you need to specify all the borders in order to get a match.

Please Login or Register  to view this content.

Also not the Top edge border is fussy about where in the code it sits.

**spinner99** · 03-12-2009, 12:10 PM

Andy - thanks a lot it works fine now - how on earth did you work out where the edge top had to go??

**Andy Pope** · 03-12-2009, 12:15 PM

I range a section of the code, setting 1 borders format. And then manually check the state of the Find dialog.
I could see that setting the top along would do so correctly, that is double line style.
When I did both top and left and then checked the dialog I could see the top had reverted to Single line. This bug was stopping the find from working.

**RandalFlagg** · 08-26-2011, 02:33 PM

How would I adapt that to detect the border style and then change the weight from xDouble to xContinuous for an entire worksheet?

I'm wanting to incorporate that function into a loop I have already written that goes through an entire workbook making various formatting changes to each of the worksheets.

My problem stems from the border style being xDouble, which is an eyesore to our admin department and unfortunately I cannot change how I receive the excel file. So I have written various macros to fix the problems that have arisen but cannot for the life of me figure out the border problem.

If you could push me in the right direction i'd be much appreciated! As this is the first forum i've found with a partial solution available. I only need to adapt it a bit to make it work in my case.

Thanks in advance.

Tim

**RandalFlagg** · 08-26-2011, 04:09 PM

Just found a workable solution. Thought you might like to see how I did it!

This will circulate through the active sheet and adjust the border style based on your preference.

Sub FixBorders()
For Each h In ActiveSheet.UsedRange.Cells
For i = 5 To 10
If h.Borders(i).Weight <> xlThin Then _
h.Borders(i).Weight = xlThin
Next
Next
End Sub

**Mordred** · 08-26-2011, 04:11 PM

Hi RF, please read the forum rules. Specifically:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Mordred's a Hungry!