BEST suggestion to keep formats?

[CAPTGNVR]

DEAR ALL

I have a sheet full of names, dates and particulars. Over a period of time
some parts of the sheet is copied and dragged down, some cells erased etc
using 'clear contents' in VB .

Invariably some of the cell loses its format and few will be aligned center
and some left and some right. Since the sheet is protected, can anyone
suggest what is the best way to keep the format that was made for each column
regardless of the shifting and deleting data.

VB is not a solution bcos I dont intend to sit and write alignment code for
100 columns and hence pls help - is there a way out?

brgds/captgnvr

 

[Rick Rothstein \(MVP - VB\)]

Assuming you applied your alignment formatting to the entire column and not
just to a selection of cells, then odds are nothing you have done has
affected the alignment format of the last cell in each column. If that is
the case, then this macro should realign the entries in your cells to
whatever the alignment format for the column it's in is...

Sub RealignColumns()
Dim C As Range
For Each C In Columns
C.HorizontalAlignment = Cells(Rows.Count, C.Column).HorizontalAlignment
Next
End Sub

Rick

 

[Mark Ivey]

Here is one thought...

Manual process...

1. Unprotect your sheet
2. Find one row that meets your requirement for all your formats and
highlight the entire row
3. Right click and select COPY
4. Highlight all the other rows that need fixing
5. Right click and select PASTE SPECIAL, select FORMATS, and then OK

Walaaa...

Let me know if this helps out...

Mark

 

[Mark Ivey]

You could even set up the Macro Recorder to capture these steps if this
became a frequent issue...

Mark Ivey

 

[Rick Rothstein \(MVP - VB\)]

I just read Mark's posting and realized I forgot the sheet is protected.
Here is a modification of my code to handle that...

Sub RealignColumns()
Dim C As Range
With Worksheets("Sheet1")
.Unprotect
For Each C In .Columns
C.HorizontalAlignment = .Cells(Rows.Count, C.Column). _
HorizontalAlignment
Next
.Protect
End With
End Sub

Note I assumed Sheet1 is the worksheet we are doing this to... change that
as needed. Also, look at the help files for the Protect and Unprotect method
as you may have to account for passwords and/or protection settings other
than the default ones.

Rick

 

[CAPTGNVR]

D/RICK

Slight language understanding problem. "then odds are nothing you have done
has

affected the alignment format of the last cell in each column. If that is
the case, then this macro should realign the ......"

I understand that if I have formatted the complete column then regardless
whatever deleting and clearing contents I do the format should not change--.
Did i understand it correctly??

Secondly, the macro code, how to know how is it aligning (left, right,
middle) bcos I read only horizontal alignment.

Thirdly, where shall i attach this macro.

I would have responded earlier but firefox was not opening the answer and
finally i had to open in IE to see ur help. Thanks for your prompt help.
Pls help little further.

brgds/captgnvr

 

[CAPTGNVR]

D/MARK
I have done as u have sugested and it looks fine as of now. So regardless
of what I do in VB like clearing contents, shifting block up a row or down a
row the formats will remain same??

One change from your suggestion. I did not mark the entire row bcos I have
different format after 20 columns. I only highlighted the first row of 20
column and marked next 100rows and pastespecial for formats.

pls advice.

brgds/captgnvr

 

[Mark Ivey]

That should work as well...

You can set up the Macro Recorder to capture your events and use them again
and again if you see a need for repetition

I am not sure I totally follow... Does this solution fit your needs or were
you needing a different technique/solution?

Mark Ivey

 

[Rick Rothstein \(MVP - VB\)]

See inline comments...

Slight language understanding problem. "then odds are nothing you have
done
has

I understand that if I have formatted the complete column then regardless
whatever deleting and clearing contents I do the format should not
change--.
Did i understand it correctly??

You can override the formatting of a column by copy/pasting or dragging a
cell or range of cells with a difrerent formatting into that column.

Secondly, the macro code, how to know how is it aligning (left, right,
middle) bcos I read only horizontal alignment.

HorizontalAlignment property of a Range can be set to be either Left, Center
or Right justified. The active line of code simply looks at the alignment
for the last cell in the column and applies it to the entire column which,
in turn, "fixes" and cells having a different alignment. As I said, nothing
you said you are doing on the worksheet should ever have changed any
formatting for the cells on the very last row of the worksheet; so I simply
used that as a template for the realigning the entire column.

Thirdly, where shall i attach this macro.

Right-click the tab for the worksheet and select View Code from the popup
menu that appears. Copy/Paste my code into the code window that appeared
automatically. Be sure to use the last code I posted as it addresses the
fact that the worksheet is protected (something I left out of my original
code). And also be sure to change my reference to "Sheet1" in the With
statement to the actual name of your worksheet (and remember that name needs
to be in quotes like the sample I showed in my code).

Thanks for your prompt help.

You are welcome.


Rick

 

[CAPTGNVR]

D/RICK

Thanks for the detailed explanation and the time u hv devoted for this. It
was great help.

brgds/captgnvr

 

[CAPTGNVR]

D/MARK
Thanks for the solution. Yes finally I have recorded a macro by settign the
format to the required columns and copying the format to the named range.
This macro is called in the program when final sprucing is done and all fine.

Also got another nice alternative from Mr. Rick to keep the last cells in
the columns and run the code.

CAN CONSIDER THIS THREAD CLOSED.

brgds/captgnvr