binar
08-28-2011, 08:58 AM
Fellow Forum Members,
I need help making some changes to the VBA script below. The script below makes it posstible to have different formatting in different parts of the result string in column C. This script is necessary because using a Concatenate function that looks like this =Concatenate(A1," - ",B1) results in losing applied special formatting in the results.
My special formatting in Column A and B is as follows:
Column A has old numbers with leading zeroes like: 0074, 0088, 0001, 0754, 0444. These leading zeroes are generated using Portugal Zip Code column formatting. Also this column has special FONT formatting consisting of RED color fonts and a horizontal line across the text known as a STRIKETHROUGH.
Column B has new numbers with leading zeroes generated using Portugal Zip Code column formatting that look like this: 0064, 0068, 0007, 0554, 0344. These numbers have no special FONT formatting applied. They are just BLACK and have no STRIKETHROUGH.
I need the script not to drop from column A and B the leading zeroes from the result in column C. Also not to drop from column A the RED font color and the Strikethrough in the results shown in column C. One last item I also need changed is the dash shown in the code as " - ". I would like to replace it with a carriage return obtained by using the code "CHAR(10)". I have not been able to add a carriage return so that the old red number with the strikethrough appears on the first line of Cell C1 and the new number without the strikethrough appears on the second line of Cell C1. In othe words, it's the same result that is obtained by using Alt+Enter within a cell to divide a text string into two lines within the same cell.
Sub KeepFormat()
Dim r As Range, i As Long
Set r = Range("a1").CurrentRegion.Resize(, 3)
For i = 1 To r.Rows.Count
With r.Cells(i, 3)
.Value = r.Cells(i, 1) & " - " & r.Cells(i, 2)
With .Characters(1, Len(r.Cells(i, 1)))
.Font.Color = 255
.Font.Strikethrough = 1
End With
End With
Next
End Sub
Any help in making the modifications I need done to the script above will be greatly appreciated. Thanks.
I need help making some changes to the VBA script below. The script below makes it posstible to have different formatting in different parts of the result string in column C. This script is necessary because using a Concatenate function that looks like this =Concatenate(A1," - ",B1) results in losing applied special formatting in the results.
My special formatting in Column A and B is as follows:
Column A has old numbers with leading zeroes like: 0074, 0088, 0001, 0754, 0444. These leading zeroes are generated using Portugal Zip Code column formatting. Also this column has special FONT formatting consisting of RED color fonts and a horizontal line across the text known as a STRIKETHROUGH.
Column B has new numbers with leading zeroes generated using Portugal Zip Code column formatting that look like this: 0064, 0068, 0007, 0554, 0344. These numbers have no special FONT formatting applied. They are just BLACK and have no STRIKETHROUGH.
I need the script not to drop from column A and B the leading zeroes from the result in column C. Also not to drop from column A the RED font color and the Strikethrough in the results shown in column C. One last item I also need changed is the dash shown in the code as " - ". I would like to replace it with a carriage return obtained by using the code "CHAR(10)". I have not been able to add a carriage return so that the old red number with the strikethrough appears on the first line of Cell C1 and the new number without the strikethrough appears on the second line of Cell C1. In othe words, it's the same result that is obtained by using Alt+Enter within a cell to divide a text string into two lines within the same cell.
Sub KeepFormat()
Dim r As Range, i As Long
Set r = Range("a1").CurrentRegion.Resize(, 3)
For i = 1 To r.Rows.Count
With r.Cells(i, 3)
.Value = r.Cells(i, 1) & " - " & r.Cells(i, 2)
With .Characters(1, Len(r.Cells(i, 1)))
.Font.Color = 255
.Font.Strikethrough = 1
End With
End With
Next
End Sub
Any help in making the modifications I need done to the script above will be greatly appreciated. Thanks.