need help to clean up

This is what I want my code to do. Find a cell that has a border on the left and has a light green fill. I want it to remove the border on the left and right for that cell and the two below it. I want this to repeat in the range. Im new at this stuff and tried to make a code. but its not working :( any help?

I used the record macro in Excel to help me a bit. I think some of my structures are wrong :( When i try to hit f8 it gives me a syntax error

Dim cell As Range
For Each cell In Range("P8:P200")
If cell.Border (x1EdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
And cell.Interior
.ColorIndex = 35
.Pattern = xlSolid
Then

Selection.borders(xlEdgeLeft).LineStyle = xlNone
Selection.borders(xlEdgeRight).LineStyle = xlNone
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.borders(xlEdgeLeft).LineStyle = xlNone
Selection.borders(xlEdgeRight).LineStyle = xlNone
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.borders(xlEdgeLeft).LineStyle = xlNone
Selection.borders(xlEdgeRight).LineStyle = xlNone
End If
Next cell

Don't worry, you're definitely on the right track. Unfortunately, the If statement isn't flexible enough with the style of input you're trying to use (it would be really nice if it were, though). You have to join up each comparison with an And, like so:
Dim cell As Range
For Each cell In Range("P8:P200")
With cell
If (.Borders(xlEdgeLeft).LineStyle = xlContinuous) And _
(.Borders(xlEdgeLeft).Weight = xlThin) And _
(.Borders(xlEdgeLeft).ColorIndex = xlAutomatic) And _
(.Interior.ColorIndex = 35) And _
(.Interior.Pattern = xlSolid) Then

'do something

End If
End With
Next cell

Doesn't look very pretty, huh? :eek:

You can make it a bit easier to follow if you evaluate the conditions in several steps, like so:
Dim doesHaveLeftBorder As Boolean, doesHaveGreenInterior As Boolean
Dim cell As Range
For Each cell In Range("P8:P200")

With cell.Borders(xlEdgeLeft)
doesHaveLeftBorder = (.LineStyle = xlContinuous) And (.Weight = xlThin) And (.ColorIndex = xlAutomatic)
End With
With cell.Interior
doesHaveGreenInterior = (.ColorIndex = 35) And (.Pattern = xlSolid)
End With

If doesHaveLeftBorder And doesHaveGreenInterior Then
'do something
End If

Next cell

I'll leave it at that, for now, the second step should be easier to extrapolate from the macro recorder output. :)

Thanks so much for your reply Cas. It wasnt really working right(it was jumping columns) so I took out a few of the details it was looking for. Problem now is that its not looping. I have to keep hitting F8 and re-running the macro for it to go down the column.
And it also isnt recognizing the Range N8:N200. Its only removing borders from whatever the column the active cell is in.

If I have active cell in C3 and run this macro it removes cells in Column C's borders :( Erg...so frustrating to learn this stuff!


Sub borderremove()

Dim cell As Range
For Each cell In Range("N8:N200")
With cell
If (.Interior.ColorIndex = 35) Then


Selection.BORDERS(xlEdgeLeft).LineStyle = xlNone
Selection.BORDERS(xlEdgeRight).LineStyle = xlNone
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.BORDERS(xlEdgeLeft).LineStyle = xlNone
Selection.BORDERS(xlEdgeRight).LineStyle = xlNone
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.BORDERS(xlEdgeLeft).LineStyle = xlNone
Selection.BORDERS(xlEdgeRight).LineStyle = xlNone

End If
End With
Next cell


End Sub

The macro recorder will generally produce code that operates on the Selection, meaning the cell or range that (usually) has been selected by the user. That's due to the way the user interacts with Excel - to enter a value into a cell, we first select the cell, then enter the value. For most operations, this is the only available method.

When you write your own code, you hardly ever want to do things this way, though, both because it's inefficient and because it's unnecessarily complicated. Unlike the user, the programmer has the ability to modify cells without selecting them first - by directly addressing them, using Range objects.

So, whenever you use code generated by the recorder, take out all the Selection qualifiers and replace them by the appropriate Range objects.

I trust I'm not being too cryptic... :)

Ahh I understand what you mean..but

I tried it like this


Sub borderremove()

Dim cell As Range
For Each cell In Range("N8:N200")
With cell
If (.Interior.ColorIndex = 35) Then

Range("N8:N200").BORDERS(xlEdgeLeft).LineStyle = xlNone
Range("N8:N200").BORDERS(xlEdgeRight).LineStyle = xlNone
ActiveCell.Offset(1, 0).Range("A1").Select
Range("N8:N200").BORDERS(xlEdgeLeft).LineStyle = xlNone
Range("N8:N200").BORDERS(xlEdgeRight).LineStyle = xlNone
ActiveCell.Offset(1, 0).Range("A1").Select
Range("N8:N200").BORDERS(xlEdgeLeft).LineStyle = xlNone
Range("N8:N200").BORDERS(xlEdgeRight).LineStyle = xlNone


End If
End With
Next cell


End Sub


It removes the whole columns borders. GRRR I thought it worked. But looking at it again...sigh :( I was flipping thru my VBA book for dummies and cant figure out how to reference it instead of "Selection" word. I tried Cell.borders and that didnt work :( I thought with that "Dim" phrase I was labeling the range I wanted to work with already. Man this stuff is so confusing to learn!

back to the drawing board! :mad:

Grrr, I'd hate to just give you the answer. :)

Let's do this step by step. In the context of your loop, there are three ranges that are of interest to you, namely

Range1 = The entire range you want to look at,
Range2 = the cell you're currently looking at, and
Range3 = the range you want to modify depending on that cell.

I've deliberately chosen names different from those you have in your code so far, to make it clear that all of these are the same type of thing.

Now, Range1 is fixed, so that's the easy part - it's just N8:N200. To create a Range object encompassing this range, we just need an assignment:
Dim Range1 As Range: Set Range1 = Range("N8:N200")

So far, so good.

Now, Range2 isn't fixed, so you can't create it directly with an A1-type address like the first. But VBA gives us a nice structure which allows us to create a range from each cell in another range in turn, namely the for...each loop:
Dim Range2 As Range
For Each Range2 In Range1
'...
Next

This is precisely equivalent to the following:
Dim Range2 As Range

Set Range2 = Range("N8")
'...

Set Range2 = Range("N9")
'...

'etc

Set Range2 = Range("N200")
'...

where "..." is the same piece of code each time. As you'll agree, the loop is much more convenient than having to copy everything 200 times.

So, since Range2 is each cell in Range1 in turn, Range2 is exactly what we want to use in the If-statement. You with me so far? :)

Finally, we need to find a way to create Range3 from Range2. In your own words, Range3 is
that cell Range2 and the two below it
So, how do we do that? Well, there are many different ways of achieving this result. The macro recorder suggests using the Offset method to select each of the three cells individually, and that works allright.
But a simpler way would be to just extend Range2 to include those two cells below it. And, in fact, the Range object provides a method that does just that - it's called Resize. If you type Resize in the VB editor, select it and press F1, it shows you an explanation of what the method does and how it works:

Returns a Range object that represents the resized range.

expression.Resize(RowSize, ColumnSize)

expression Required. An expression that returns a Range object to be resized.

RowSize Optional Variant. The number of rows in the new range. If this argument is omitted, the number of rows in the range remains the same.

ColumnSize Optional Variant. The number of columns in the new range. If this argument is omitted, the number of columns in the range remains the same.

Okay, so, what does it do? "Returns a Range object that represents the resized range" - super, that's what we want, that's our Range3.
How does it work? It needs three things:
- "a Range object to be resized." - no problem, we have that, that's our Range2.
- "number of rows in the new range" - that's the whole point, we want this to be 3.
- "number of columns in the new range" - we don't want to change this, so we can either set it to 1 or omit it.

Given all this information, we can now construct the line that will produce Range3:
Set Range3 = Range2.Resize(3,1)

Excellent, so we can create the three Range objects we need. All that's left is to put the code together so that it does what we want:
'declare the ranges, using dim
'create Range1
'loop over each Range2 (cell) in Range1, using for...each
' if Range2 passes the test, then
' create Range3 from the current Range2, using .resize
' modify Range3, ref the macro recorder output
' end if
'end loop

Now, at this stage, having hopefully made the point that the three thingies are all the same sort of thingy, namely Ranges, it might be a good idea to change the names to something more explanatory than "Range1" thru "Range3" when you try to implement this... something along the lines of "myRange", "cellToBeTested" and "cellsToBeModified", maybe? But that part is really up to you, whatever makes the code easy to read is good.

Let me know how you get on! :)

you are so helpful! I understand what you mean about the different ranges and the naming and such. Im going to work on this. I will play around with this and post how it goes!

your awesome Cas! :)

Yahhhhhhh!!!!


Sub BorderRemoveNew()

Dim FullRange As Range: Set FullRange = Range("N8:N200")
Dim GreenRange As Range
For Each GreenRange In FullRange
With GreenRange
If (.Interior.ColorIndex = 35) Then

Set BorderRange = GreenRange.Resize(3, 1)
BorderRange.BORDERS(xlEdgeRight).LineStyle = xlNone
BorderRange.BORDERS(xlEdgeLeft).LineStyle = xlNone


End If

End With
Next
End Sub



It works perfectly! I owe you a dozen cookies Cas! :D Im starting to get a better grasp on the powers of VBA. To bad my local community college doesnt have a summer course on VBA :(

Can you tell me whats wrong with this code? I got it from my VBA for Dummies book :(


Range(ActiveCell, ActiveCell.End(x1Down)).Select


Im getting a runtime error 1004. When I try and read the help file it just tells me how to trap an error :o The whole code is like this (I wrote the end, the find part was from macro recorder :P)


Cells.Find(What:="Equity Trading", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(-6, 3).Activate
Range(ActiveCell, ActiveCell.End(x1Down)).Select

Selection.BORDERS(xlEdgeRight).LineStyle = xlNone
Selection.BORDERS(xlEdgeLeft).LineStyle = xlNone


I am assuming that since I am selecting the range I can use the Selection.Borders part. :) But that one line of code is stopping me from making it work :(

It works perfectly! I owe you a dozen cookies Cas!
Excellent! And I'll hold you to that... ;)

As to the next problem, I know exactly what's wrong, but before I tell you, let me ask you this - how did you produce that line? Did you type it character by character from the book?

Yes. I looked at it and didnt see any variable in it that I was suppose to change :huh:

"the following VBA procedure selects the range beginning at the active cell and extending down to the cell just above the first blank cell in the column."

thats what the books says it does :( Maybe I'm having a blond moment :-\

Range(ActiveCell, ActiveCell.End(x1Down)).Select
The problem is quite simply that your "1" (one) is supposed to be a lower-case "l" (ell). When you use the VB Editor, it'll often pop up little dropdown lists or tooltip texts to guide you along, to avoid just that sort of typo-problem. In this case, one should have appeared once you typed the open parenthesis following "End". Use these! :)

The best prevention for errors of this kind is actually to put "Option Explicit" at the top of each code module. That will inform VB that you intend to declare all variables, and will make it complain if you're trying to use something that's neither a constant (like xlDown) nor a variable you have declared. You can instruct the editor to put this line at the top of each new module by setting the Tools->Options->Editor->Require Declaration option.

HTH (http://dictionary.reference.com/browse/HTH)

Wow I put a whole new meaning to "blond moment" ...
I feel dumb now :(

I wrote down these tips you gave me. I'll play around with this declaration thing. I was probably to busy staring at the book while typing to realize my mistake! Now I owe you a bakers dozen of cookies :p

I feel dumb now
There's really no need. Everyone makes typos. The problem with VB is that with its standard settings, it lets you carry on and then gives you an unhelpful error message at some point. Or, even worse, it can also not throw an error at all but eventually come up with a wrong result, and that's yet ten times harder to track down.
That's why "Use Option Explicit" is one of the best pieces of advice one can give to a beginning VB programmer - it may seem like a bit of hassle to declare all variables at first, but it really makes the Editor help you at the end of the day.

hrm it fixed the error but the code didnt work. I rewrote it and this one works!!! Im catching onto this VBA stuff :D Ill be a VBA geeklet yet!


Sub RemoveRestOfBorders()

Cells.Find(What:="Equity Trading", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(-6, 3).Activate

Range("n200", ActiveCell.Address).Select


Selection.BORDERS(xlEdgeRight).LineStyle = xlNone
Selection.BORDERS(xlEdgeLeft).LineStyle = xlNone

End Sub

Option Explicit = training wheels

Much safer after skinning my knees today

:chuckle: