Formatting Data with VBA

[Notadumbblonde]

Hey Excels, Got a interesting problem.

I have a spreadsheet that all my staff keys into

I constantly do range value (sorry Lotus Term) copy special value, and then format the numbers.

Is there any way I can code:

1. To make the range I'm extracting numbers!! if currently Strings
2. Make them a predefined format, say 1 decimal point.

Any Help would greatly be appreciated.

I make use conditional formatting, but my users may be cutting and pasting from text files.

 

[WillR]

What would you like to happen if you have cells containing error values?

 

[WillR]

If you just want to let the user know, you could just show a message box

Sub nonblondeformat()
For Each cell In Range("A1:A20").Cells
 If IsNumeric(cell.Value) Then
  With cell
  .Value = .Value * 1
  .NumberFormat = "#,##0.0;(#,##0.0)"
  End With
 ElseIf IsError(cell.Value) Then
  MsgBox "Cell " & cell.Address & " is an error "
 Else
  With cell.Interior
  .ColorIndex = 6
  .Pattern = xlSolid
  End With
 End If
Next cell
End Sub