Reusable VBA macro to prevent Excel from destroying the data (+ backup capacity)
Overall, your first program in VBA looks pretty good. I've made some modifications below to illustrate a few points that may help you in the future.


Using Call to call the CreateBackup sub is not necessary and is generally not used much anymore. (However, on a single line like this, I've seen it used to more clearly show that another sub is performing work.)
Indenting your code may have gotten messed up when you posted here to SO, but the code below has each logical level indented to highlight where the different logic paths go.
You re-used cell1 for both the UsedRange and the individual cell value within your loop. That's a big no-no. Use specific names to clearly note what each variable refers, such as thisCell and allCells.
Nearly all the references within your first loop are to thisCell. In this situation, encapsulate the code in a With block. This compiles to a common variable reference without having to reevaluate the fully specified name each time. It also gives you somewhat faster executing code.
Separate your logic into levels to make it easier to follow. In your case, when the cell value is empty and when it's not. Then again when the NumberFormat is not text. It's easy to get lost in a single If statement that has many different parts.
Finally, you're making multiple character replacements at the end and you might encounter other character-pairs that need replacing too. To make your code more easily expandable, the find and replace characters can be added to arrays and accessed in a loop. Only the array assignment would change then.


Option Explicit

Sub PrepareForCSV()
    CreateBackup
    Dim thisCell As Range
    Dim allCells As Range
    Set allCells = ActiveWorkbook.ActiveSheet.UsedRange
    '--- PART 1:
    '    Goal: to accomplish setup described here
    '          http://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates

    For Each thisCell In allCells
        With thisCell
            '--- inserting - sign to avoid blank cells within UsedRange
            '    if the cell is empty...
            If IsEmpty(.Value) Then
                If .NumberFormat &lt;&gt; "@" Then
                    '--- if the format is not text
                    .Value = "=" &amp; """" &amp; "-" &amp; """"
                    .NumberFormat = "@"
                Else
                    '--- if the format is text
                    .Value = "-"
                    .NumberFormat = "@"
                End If

            ElseIf .NumberFormat &lt;&gt; "@" Then
                '--- if the cell isn't empty and not formatted as text
                If InStr(1, .Value, """=") = 0 Then
                    '--- starts with "=
                    .Value = "=" &amp; """" &amp; cell1.Value &amp; """"
                    .NumberFormat = "@"

                ElseIf InStr(1, .Value, """") = 0 Then
                    '--- starts with "
                    .Value = "=" &amp; """" &amp; cell1.Value &amp; """"
                    .NumberFormat = "@"
                Else
                    '--- do nothing?
                End If
            Else
                '--- do nothing?
            End If
        End With
    Next

    '--- PART 2: replace all unwanted characters
    Dim fnd() As Variant
    Dim rplc() As Variant
    Dim i As Integer

    '--- replace characters which may cause errors when csv is imported
    fnd = Array(":", """""", """""")
    rplc = Array("!", """", """")

    For i = 1 To UBound(fnd, 1)
        ActiveWorkbook.ActiveSheet.Cells.Replace What:=fnd(i), Replacement:=rplc(i), _
                                                 LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                                                 SearchFormat:=False, ReplaceFormat:=False
    Next i
End Sub