Search and format only cells that contain "*".

[jsprott]

I have a worksheet that is almost complete. For printing and visual preference, there is something that I would like to implement. When my data is entered, the cells that have no data entered into them return a " * " value. This value is different for every row, so as the rows get entered, I'd like the " * " to be centered in the cells that they are in.

I'm still learning Excel 2003 VB, so any help would be great.
Thanks

[stnkynts]

I assumed only column A since you didn't specify.

Sub Center_Stars()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1") 'you may need to change this
Dim lastrow As Long
Dim icell As Range

lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row

For Each icell In ws.Range("A1:A" & lastrow)
    If icell.Value = "*" Then
        icell.HorizontalAlignment = xlCenter
    End If
Next icell

End Sub

[jsprott]

No it is going to be Row 2 only. The data is entered on sheet1 and "submitted" (copy & pasted) to sheet2 in Row 2. All of the rest of the columns shift down when the new column is "submitted". This is where & when I would like the formatting to happen, before the VB is set to change sheets.

[jsprott]

No it is going to be Row 2 only. The data is entered on sheet1 and "submitted" (copy & pasted) to sheet2 in Row 2. All of the rest of the columns shift down when the new column is "submitted". This is where & when I would like the formatting to happen, before the VB is set to change sheets.

Sorry I had to change some info.

[stnkynts]

Sub Center_Stars()
Dim ws As Worksheet:    Set ws = Sheets("Sheet2")
Dim lastcol As Long
Dim icell As Range

lastcol = ws.Cells(2, Columns.Count).End(xlToLeft).Column

For Each icell In ws.Range(Cells(2, 1), Cells(2, lastcol))
    If icell.Value = "*" Then
        icell.HorizontalAlignment = xlCenter
    End If
Next icell

End Sub

[jsprott]

There seems to be an issue with the "For Each" line. when I run the debug, it gives me an application error.

[jsprott]

Temp.xlsmHere is a copy of what I've got. Don't quite know what's going wrong.

[stnkynts]

You needed to change the sheet name in the code to match yours.

Sub Center_Stars()
Dim ws As Worksheet:    Set ws = Sheets("Data")
Dim lastcol As Long
Dim icell As Range

lastcol = ws.Cells(2, Columns.Count).End(xlToLeft).Column

For Each icell In ws.Range(Cells(2, 1), Cells(2, lastcol))
    If IsError(icell) Then
    'do nothing
    Else
    If icell.Value = "*" Then
        icell.HorizontalAlignment = xlCenter
    End If
    End If
Next icell

End Sub