Having trouble with "IF" macro

[Winnietp]

Like several others, this is the first time I’ve tried creating a macro. I’ve solved several problems that have popped up, but now I’m stuck. I’m trying to get the macro to find every row where column A has text and column B is empty. Whenever that occurs (should be a total of four times), the entire row has to have a border at the top, needs to be highlighted in blue, and the font is bold.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’m fairly confident this should be accomplished with an “IF” macro, but I just haven’t been able to figure it out.
<o> </o>
I think the macro should st<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:PersonName w:st="on">art</st1:PersonName> out with something like this:
<o> </o>
Range("A1").Select
If Range(“A1”) = “xxx” And (“B1” = "") Then
<o> </o>
Using Excel 2003, I ran the macro based on a specific row (see below), but that won’t be an option for the actual report.
<o> </o>
Sub DesignRow()<o></o>
'
' DesignRow Macro
<o> </o>
'
Rows("12:12").Select
With Selection.Interior
.ColorIndex = 33
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDot
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
End With
End Sub
<o> </o>
<o> </o>
Thanks in advance for any and all help.

 

[repairman615]

Hello,

Maybe like:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DesignRow()<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' DesignRow Macro</SPAN><br><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> cRange <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Set</SPAN> cRange = Rows("12:12")<br><br>    <SPAN style="color:#00007F">If</SPAN> Range(“A1”).Value = “xxx” And Range(“B1”).Value = "" <SPAN style="color:#00007F">Then</SPAN><br>    <br>        <SPAN style="color:#00007F">With</SPAN> cRange.Interior<br>            .ColorIndex = 33<br>            .Pattern = xlSolid<br>            .PatternColorIndex = xlAutomatic<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> cRange.Borders(xlEdgeTop)<br>            .LineStyle = xlDot<br>            .Weight = xlThin<br>            .ColorIndex = xlAutomatic<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> cRange.Font<br>            .Name = "Arial"<br>            .FontStyle = "Bold"<br>            .Size = 10<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Generally select is "bad".

-Jeff

 

[Michael M]

Ahh, Jeff your too quick for me !!

Hi Winnietp and Welcome to the Board
First off, a couple of tips, if I may.
To get a better response to your queries, use code tags to surround your code, it makes simpler to read.
Also, try indenting your code, similar to my example, this also improves readability and makes it easier to spot errors.

Try this for your code

Sub DesignRow()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 1 Step -1
        If Range("A" & r).Value = “xxx” And Range("B" & r).Value = "" Then
            With Rows(r).Interior
                .ColorIndex = 33
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
            With rows(r).Borders(xlEdgeTop)
                .LineStyle = xlDot
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With Rows(r).Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
            End With
        End If
    Next r
End Sub

 

[repairman615]

This also seems to work nesting the With's...


Also try:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DesignRow()<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' DesignRow Macro</SPAN><br><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> cRange <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> maybeXXX <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> cRange = Rows("12:12")<br><SPAN style="color:#00007F">Set</SPAN> maybeXXX = "XXX" <SPAN style="color:#007F00">'''Here is where to set the text to look for in cell A1</SPAN><br><br>    <SPAN style="color:#00007F">If</SPAN> Range("A1").Value = maybeXXX And Range("B1").Value = "" <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> cRange<br>            <SPAN style="color:#00007F">With</SPAN> .Interior<br>                .ColorIndex = 33<br>                .Pattern = xlSolid<br>                .PatternColorIndex = xlAutomatic<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> .Borders(xlEdgeTop)<br>                .LineStyle = xlDot<br>                .Weight = xlThin<br>                .ColorIndex = xlAutomatic<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> .Font<br>                .Name = "Arial"<br>                .FontStyle = "Bold"<br>                .Size = 10<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Michael M]

Winnie
Keep in mind that you are formatting the entire row which will bloat your workbook, create a huge amount of printing and generally be a pain in the ....you know.
It might pay to restrict your range to the last column, say A1:G1, for example.

 

[repairman615]

Michael,

Yours is more acurate! I missed the part about the rows... maybe blend??

 

[Winnietp]

Hi Guys,

Thanks for your responses. Not only is this my first attempt at creating a macro, it’s the first time I’ve posted a question online. Very exciting for a 50+ lady.

That said, Michael’s macro worked to a point. For some reason it applied the changes to the rows where both column A and B were empty. It should have occurred when column A had text and B didn’t.

Unfortunately I don’t understand the syntax well enough to know why this is happening, but I’ll continue to play around with it.

Thanks again for your help with my macro and tips on posting, very appreciated.

 

[Michael M]

Winnie
I note your Quotation marks are not "normal"
I've changed them in the code and it works fine for me

Sub DesignRow()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 1 Step -1
        If Range("A" & r).Value = "xxx" And Range("B" & r).Value = "" Then
            With Rows(r).Interior
                .ColorIndex = 33
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
            With Rows(r).Borders(xlEdgeTop)
                .LineStyle = xlDot
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With Rows(r).Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
            End With
        End If
    Next r
End Sub

 

[repairman615]

I note your Quotation marks are not "normal"

I found that to be true also. After I replaced with regular quotes it worked for me.


Winnie,
Notice the difference from your Original Post in this thread:

Range("A1").Select
If Range(“A1”) = “xxx” And (“B1” = "") Then

Green works, Red did not work.

 

[Michael M]

Thanks Jeff....it must be because I'm waaay over 50, and my eyesight ain't so good anymore !!!