Where do I start

[MichaelRSnow]

I want to build a macro to attach to command button which formats cells within another sheet. for example...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Sheet1 - contains macro button<o></o>
Sheet2 - contains data, cell ranges from A2-L1000<o></o>
<o></o>
I want macro to go to sheet2 then, 1. change all text to = blue, 2. change borders to = all borders, 3. change text alignment to left or centre, example A2:A1000 = left, B2:B1000 = centre?<o></o>
<o></o>
Not sure where to start on this one so help would be appreciated?<o></o>

 

[Peter_SSs]

A start for most macros is to turn on the macro recorder and do the steps manually then turn off the recorder. This usually produces fairly poor code, but it is a starting point.

You can then try to tidy the code and/or generalise it and if you need help with that, post the code, or relevant parts of it, with details of what you need help with.

 

[MichaelRSnow]

A start for most macros is to turn on the macro recorder and do the steps manually then turn off the recorder. This usually produces fairly poor code, but it is a starting point.

You can then try to tidy the code and/or generalise it and if you need help with that, post the code, or relevant parts of it, with details of what you need help with.

I tried that but could get it to work once I attached it to a mcaro.

 

[Peter_SSs]

I tried that but could get it to work once I attached it to a mcaro.

What did happen when you attached it to the macro button? Did it
- do the tasks on Sheet1 instead of Sheet2?
- do som eother wrong thing? What?
- do nothing?
- give an error?

I suggest you try again and just record the font colour change. If you still cannot get that to work, post the code you have (see my signature block re posting code) and explain what went wrong (similar questions to those I asked above).

We can then probably nudge you in the right direction and then you can move on to the borders etc.

 

[MichaelRSnow]

ADVERTISEMENT

It doesn't do anything at the moment, no errors but no changes.

I've been tweaking it since i recorded one and it is now like so....the more i tweak the worse it get though.

With Sheets("Withdrawn Products")
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Font.ColorIndex = 5
End With
With Sheets("Withdrawn Products")
Range("A1:A2000").Select
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End With
With Sheets("Withdrawn Products")
Range("B1:B2000").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End With

 

[Kaps_mr2]

I suggest that you build your macro in stages (using the macro recorder). i.e first write the macro that goes to sheet 2. Once you're happy with this then develop the bit that changes all the text to blue etc. thanks

Kaps

 

[Peter_SSs]

Here's a couple of snippets. See if they help you progress. Note the '.'s that I have highlighted red. Without those, the range will refer to whatever the active sheet is (not 'Withdrawn Products' if your button is on another sheet.

Do you have your code directly in the command button click code or is this a separate macro that the command button calls?

Also, when posting code, please post it with the indentation formatting, it is much easier then to read and de-bug. My signature block has a couple of suggestions for how to do that.

With Sheets("Withdrawn Products")
    With .Range("A2:L2000")
        .Font.ColorIndex = 5
    End With
    With .Range("A1:A2000")
        .HorizontalAlignment = xlLeft
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End With

 

[MichaelRSnow]

Thanks Peter, i'll work on that. cheers