Using macro to insert row above and format cells

[AdRock]

Hello

I am new to using macros and have made my first simple one today to resize images becuase I have a lot of repetive formatting to do on my spreadsheets.

What i need to do is look at a cell in column A and see if the cell cntents is equal to "Sq". if it is, I need to insert an empty row above but also make the whole row (with "Sq" in there) bold so it looks like a heading.

I haven't used VB in about 5 years but will understand the code.

There is another macro that i think i could try and build on from that, and that is for a different worksheet. I presume thi swould use a simple case statement. If column A has a certain value, then again the whole row is made bold and font colour changes. I would need to add another check on, say column B to see if the value is equal to zero. If it is ,the same again where it formats the font as bold and changes the font colour.

Can anyone please help me write a macro that will format my worksheet based on some conditions and also insert rows on aother worksheet?

 

[VoG]

Try

Sub Sq()
Dim Found As Range
Set Found = Columns("A").Find(what:="Sq", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub
Found.EntireRow.Font.Bold = True
Rows(Found.Row).Insert
End Sub

 

[AdRock]

Many thanks VoG

Just a couple of questions.....How do I save that in a workbook which contains just macros? I have a workbook which has my resize image macro which i can use on any workbook.

Also, how do i loop through each row looking for "Sq"? At the moment it finds the first one, does what it's supposed to and stops.

Many thanks for your help

 

[VoG]

Try this

Sub Sq()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("A" & i).Value = "Sq" Then
        With Rows(i)
            .Font.Bold = True
            .Insert
        End With
    End If
Next i
End Sub

You could store your code in personal.xls http://www.rondebruin.nl/personal.htm

For the other formatting issue you could use Conditional Formatting http://www.contextures.com/xlCondFormat02.html

 

[AdRock]

Thanks VoG

I've managed to nearly do everythng I had to (I think)

Just a quick question about Select Case. I know how they work but the syntax is different from what i'm used to.

What i want to do is check every cell in column B and if it matches one of these in my select case, it makes the font bold and colors it.

I have the If statement doing what I want where it colours it blue but i can't get the select case colouring red.

Also I don't kno why it's not working now, but i had row 2 all in bold and aligned how i want. now it won't bold but is aligned properly.

Sub Sheet_1()
    Dim LR As Long, i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
 
    'Set up page margins
    With Worksheets("Sheet1").PageSetup
        .Orientation = xlLandscape
        .LeftMargin = Application.CentimetersToPoints(1.1)
        .RightMargin = Application.CentimetersToPoints(1.1)
        .TopMargin = Application.CentimetersToPoints(1.3)
        .BottomMargin = Application.CentimetersToPoints(1.3)
        .HeaderMargin = Application.CentimetersToPoints(1.3)
        .FooterMargin = Application.CentimetersToPoints(1.3)
    End With
    'Delete unwanted columns
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
 
    'Set column widths
    Columns("A").ColumnWidth = 2.29
    Columns("B").ColumnWidth = 12.14
    Columns("C").ColumnWidth = 5.14
    Columns("D").ColumnWidth = 9
    Columns("E").ColumnWidth = 1.71
    Columns("F").ColumnWidth = 5.29
    Columns("G:I").ColumnWidth = 9.29
    Columns("J").ColumnWidth = 12.14
    Columns("K").ColumnWidth = 9.86
 
    For i = LR To 1 Step -1
 
        If Range("B" & i).Value = "Truck" Then
            With Rows(i)
                'Align the cells
                Range("C" & i).HorizontalAlignment = xlRight
                Range("D" & i).HorizontalAlignment = xlRight
                Range("E" & i).HorizontalAlignment = xlCenter
                Range("F" & i).HorizontalAlignment = xlRight
                Range("G" & i).HorizontalAlignment = xlRight
                Range("H" & i).HorizontalAlignment = xlRight
                Range("I" & i).HorizontalAlignment = xlRight
                Range("J" & i).HorizontalAlignment = xlRight
                Range("K" & i).HorizontalAlignment = xlRight
                .Font.Bold = True
            End With
        End If
 
        Range("E" & i).HorizontalAlignment = xlCenter
 
        Select Case Range("B" & i).Value
            Case "WA53AFF", "WA04CHY", "WA04CHX"
                With Rows(i)
                    .Font.Bold = True
                    .Font.Color = vbRed
                End With
            Case Else
                With Rows(i)
                    .Font.Bold = False
                End With
        End Select
        If Range("C" & i).Value = "0" Then
            With Rows(i)
                .Font.Bold = True
                .Font.Color = vbBlue
            End With
        End If
 
    Next i
 
End Sub

can you please help?

 

[VoG]

That works correctly for me. Make sure that WA53AFF and so on don't have leading or trailing spaces.

 

[AdRock]

Many thanks for your quick reply

It does work but i noticed that in the spreadsheet I'm copying from into my new workbook, after the AFF etc, is a whitespace. Is there a trim function or something i can trim the whitespace.

Also I still can't get my top row to Bold even though the column widths are working correctly. Any idea why it's not now working? Could it be becuase the select case has overwritten it? If so, how do I exit the select case with the else without actually having a condition for the else?

 

[VoG]

Maybe like this

        Select Case Trim(Range("B" & i).Value)
            Case "WA53AFF", "WA04CHY", "WA04CHX"
                With Rows(i)
                    .Font.Bold = True
                    .Font.Color = vbRed
                End With
        End Select

 

[AdRock]

Many thanks again.

I thought you must be able to end a select without an exit condition

I looked online for a trim fnction but all the examples i found were way to bloated fior such a cimple function. Much easier than i thought