If the column that you want to capitalize is column A, the easiest way to do this is to add a column to the right of A. In the new column b, in cell b1 put the formula "=upper(a1)". Copy that down to the bottom of the column. Then select that entire column and paste the values (edit-pastespecial-values) onto column A. Delete column b.
Dave S. This message was edited by davers5 on 2002-09-06 10:24
(1) Insert a column beside the column that has the text. For this example, ColA is your column and ColB is the inserted column.
(2) In the first cell in ColB that corresponds to your text in ColA, enter the following formula: =Upper(A1).....assuming that you have text in A1 to convert to upper case.
(3) Copy the formula down the entire ColB. You should see all of your text from ColA now in upper case in ColB.
(4) Copy all of ColB and Paste Special in ColA--when you Paste Special be sure to choose VALUES.
(5) If your pasted ColA is correct, delete ColB.
Just change the Range in the code below to your Range. This code go's in the Sheet Module of the sheet that is to be changed. JSW
'Change the Range below to the working Range to use!
For Each c In Worksheets("Sheet1").Range("A3:AA3")
c.Value = UCase(c.Value)
The code below will work automatically for any new text added to the workbook. It will not change current text like the code above!
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Dim rng As Range
'Make all to be entered text Caps.
'Load from ThisWorkBook Module.
.EnableEvents = False
For Each rng In Source
rng.Value = UCase(rng.Value)
.EnableEvents = True
You may change the code above to work on a range, if needed. JSW