inserimento automatico dati NO elenco a tendina

ps sarebbe possibile modificare anche il colore delle singole celle per personalizzarle?

                 Case 1
                     .Value = "Casa"
                     .Interior.ColorIndex = 6  '<<=== colore cella
                     .Font.ColorIndex = 3       '<<=== colore carattere
                 Case 2
                     .Value = "Auto"
                     .Interior.ColorIndex = 10
                     .Font.ColorIndex = 9
                 Case 3
                     .Value = "Moto"
                     .Interior.ColorIndex = 1
                     .Font.ColorIndex = 6
                 Case 4
                     .Value = "Bici"
                     .Interior.ColorIndex = 8
                     .Font.ColorIndex = 1

Per vedere i colori e i rispettivi valori, copia incolla questa macro in un modulo standard di un nuovo file e poi eseguila:

 Public Sub m()
    Dim lng As Long
    With ThisWorkbook.Worksheets("Foglio1")
        For lng = 0 To 56
            .Cells(lng + 1, 1).Value = lng
            .Cells(lng + 1, 2).Interior.ColorIndex = lng
        Next
    End With
 End Sub

Trovi un esempio completo qui:

• http://www.maurogsc.eu/esempiforum14/datoperdato.zip

ho un altro piccolo problema.... come riduco o ingrandisco la dimensione del carattere mare?

    Dim rng As Range
    Dim s() As String
    Dim lng1 As Long
    Dim lng2 As Long
   
    On Error Resume Next
   
    s = Split(Target, vbNewLine)
    
     Set rng = Me.Range("A1:A10")
    
     With Target

         If Intersect(rng, Target) Is Nothing Then Exit Sub
        
         If .Cells.Count = 1 Then
             Select Case .Value
                 Case 1
                      .Value = "Casa" & vbNewLine & "mare"
                      s = Split(.Value, vbNewLine)
                      lng1 = Len(s(0)) + 3
                      lng2 = Len(s(1))
                      .Characters(Start:=lng1, Length:=lng2).Font.Size = 14
                      .Interior.ColorIndex = 1  '<<=== colore cella
                      .Font.ColorIndex = 2      '<<=== colore carattere

In grassetto le parti aggiunte. Risultato: