Auto Formatting Excel Macro

[Thejan Mendis]

Hi

I hope somebody can help me on creating a small and very helpful EXCEL
macro.

I have a excel document with table like content and I need to create a macro
which will format each cell with <td>*what ever the cell vale</td> and each
row with <tr>*cells</tr>

Example

a b c
1 2 5

<tr><td>a</td><td>b</td><td>c</td></tr>
<tr><td>1</td><td>2</td><td>5</td></tr>

if i can make apply this to selected range that will be much better


Please help me................

THEJAN

 

[Cecilkumara Fernando]

Thejan,
Assumed the activecell of the selection is the top left cell
and the column just right to the selection is empty

Sub MokatadaMay()
StCol = ActiveCell.Column
StRow = ActiveCell.row
NumOCols = Selection.Columns.Count
NumORows = Selection.Rows.Count
For i = StRow To StRow + NumORows - 1
For j = StCol To StCol + NumOCols - 1
Cells(i, StCol + NumOCols).Value = _
Cells(i, StCol + NumOCols).Value & "<td>" & _
Cells(i, j).Value & "</td>"
Next j
Cells(i, StCol + NumOCols).Value = _
"<tr>" & Cells(i, StCol + NumOCols).Value & "</tr>"
Next i
End Sub

HTH
Cecil

 

[Thejan Mendis]

hi Cecilkumara Fernando

Thanks lot, it's working perfectly and if you can please tell me how do
I put these new data (the taged cell) to a new sheet as they taged
automatically.

 

[Cecilkumara Fernando]

Thejan,
Is this what you want?

Sub MokatadaMay()
StCol = ActiveCell.Column
StRow = ActiveCell.row
NumOCols = Selection.Columns.Count
NumORows = Selection.Rows.Count
Set cursh = ActiveSheet
Sheets.Add after:=Sheets(Sheets.Count)
cursh.Activate
For i = StRow To StRow + NumORows - 1
For j = StCol To StCol + NumOCols - 1
Sheets(Sheets.Count).Cells(k + 1, 1).Value = _
Sheets(Sheets.Count).Cells(k + 1, 1).Value & "<td>" & _
Cells(i, j).Value & "</td>"
Next j
Sheets(Sheets.Count).Cells(k + 1, 1).Value = _
"<tr>" & Sheets(Sheets.Count).Cells(k + 1, 1).Value & "</tr>"
k = k + 1
Next i
End Sub

Cecil