Trova/Sostituisci formato di una stringa o parola contenuta nel testo di celle Excel

Question

E' possibile cambiare solo il colore (in generale la formattazione) di una parola contenuta in molte celle Excel?

Faccio un esempio. Nelle celle di un foglio ci sono delle celle con queste frasi:

(cella A1) numero porte patinate	(cella B1) numero finestre finitura patinata	(cella C1) finestre patinate
(cella A2) finitura patinata colorata	(cella B2) porte patinate scorrevoli	(cella C2) porte patinate battente
(...) totale porte e finestre	(...) totale porte	(...) totale finestre
(...)	(...)	(...)
(...)	(...)	(...)

Quale comando (se con CTRL+SHIFT+S, quali opzioni settare) mi può trasformare la tabella così?

(cella A1) numero porte patinate	(cella B1) numero finestre finitura patinata	(cella C1) finestre patinate
(cella A2) finitura patinata colorata	(cella B2) porte patinate scorrevoli	(cella C2) porte patinate battente
(...) totale porte e <br>finestre	(...) totale porte	(...) totale finestre

Se effettuo Trova/Sostituisci, inserisco la parola "porte" e cambio colore (qui mi sono limitato a grassetto e corsivo, ma il mio intento è cambiare anche il colore) la sostituzione avviene, ma la nuova formattazione si applica all'intera cella.

Come limitare ad una sola parola, senza alterare le altre proprietà della cella, come allineamento, colore sfondo, formattazione contenuto (numero, generale, testo, data/ora, ...), ecc ecc ...

Grazie anticipatamente,

Sebastiano

Answer 1

...

Grazie ancora, S.

Avevo pensato anch'io sarebbe stato utile selezionare il range sul quale lavorare, ecco come fare ...

--

Public Sub HighlightWords()

Dim rCheckRange As Range, rCel As Range

Dim sWord As Variant

Dim p As Long

  Const WORD_COLOR  As Long = 3           ' red

  On Error Resume Next

  '--- range da controllare

  Set rCheckRange = Application.InputBox("Seleziona il range da controllare", "Colora parole", , , , , , 8)

  If rCheckRange Is Nothing Then Exit Sub

  sWord = InputBox("Parola da ricercare e colorare", "Colora parole")

  If Len(sWord) = 0 Then Exit Sub

  sWord = Trim(sWord)

  Application.ScreenUpdating = False

  For Each rCel In rCheckRange

    p = InStr(1, rCel.Value, sWord, vbTextCompare)

    Do While p

      With rCel.Characters(p, Len(sWord))

        .Font.ColorIndex = WORD_COLOR

        .Font.Bold = True

        ' eventuali altre formattazioni

      End With

      p = InStr(p + 1, rCel.Value, sWord, vbTextCompare)

    Loop

  Next

End Sub

---

Answer 2

Ciao,

grazie ad entrambi per la risposta.

Sono un po' pratico di VBA e penso quindi di evolvere il vostro codice in modo da:

• estendere la ricerca su un Range selezionato da utente (se non sbaglio dovrei usare Set per assegnare ad una variabile il range selezionato; non ricordo però come si richiama la window di input per selezionare un range e passarlo ad una funzione o procedura); aggiunge flessibilità al codice
• poter selezionare il colore/formatting da applicare al testo che sWord prende in input.
• abilitare il tutto mediante una maschera che consenta di selezionare anche se inserire o meno il corsivo e/o il grassetto, flaggando delle checkboxes che definiscono che ulteriore formatting applicare al testo.

Mi chiedevo, c'è modo di poter caricare la palette dei colori del tema Excel 2007 adoperato correntemente? Ovvero, se volessi scegliere il color number da un array, come far si che la posizione 1 dell'array corrisponda ad es al primo colore che visualizzo nel tema corrente?

Provo a spiegarmi meglio. Normalmente la palette dei colori da cui scegliere quello da applicare a qualcosa (sfondo cella o colore carattere) è quello impostato di default in Layout di pagina, scheda "temi", colori (tema selezionato "office"). Quando apro "colore carattere" o "sfondo cella" si apre una "matrice di colori"; fatta da alcune sfumature di viola, di verde, di marrone, di grigio, .... Cambiando il tema, però, il colore ad es in 4° posizione nella matrice cambia; se si usasse un array si potrebbe associare un indice fisso ad un colore cambiato dinamicamente col tema ...

Non so se mi sono spiegato :(

Grazie ancora, S.

Answer 3

Ciao Sebastiano,

Mi sono dimenticat del corsivo!

Sostituisci:

      If CBool(iPos) Then

            rCell.Characters(iPos, iLen).Font.Bold = True

        End If

con

        If CBool(iPos) Then

            With rCell.Characters(iPos, iLen)

                .Font.Bold = True

                .Font.Italic = True

            End With

        End If

===

Regards,

Norman

Answer 4

...

Come limitare ad una sola parola, senza alterare le altre proprietà della cella, come allineamento, colore sfondo, formattazione contenuto (numero, generale, testo, data/ora, ...), ecc ecc ...

Grazie anticipatamente,

Sebastiano

E' necessario utilizzare una macro.

Prova con quella allegata, se non le hai mai utilizzate prima di tutto leggi come e dove inserire il codice macro e modifica le parti evidenziate secondo le tue necessità.

---

Public Sub HighlightWords()

Dim rCheckRange As Range, rCel As Range

Dim sWord As Variant

Dim p As Long

Const WORD_COLOR  As Long = 3           ' red

  sWord = InputBox("Parola da ricercare e colorare", "Colora parole")

  If Len(sWord) = 0 Then Exit Sub

'--- range da controllare

Set rCheckRange = ThisWorkbook.Worksheets("Foglio1").Range("a1:a9")

  sWord = Trim(sWord)

  Application.ScreenUpdating = False

  For Each rCel In rCheckRange

    p = InStr(1, rCel.Value, sWord, vbTextCompare)

    Do While p

      With rCel.Characters(p, Len(sWord))

        .Font.ColorIndex = WORD_COLOR

        .Font.Bold = True

        ' eventuali altre formattazioni

      End With

      p = InStr(p + 1, rCel.Value, sWord, vbTextCompare)

    Loop

  Next

End Sub

---

Answer 5

E' possibile cambiare solo il colore (in generale la formattazione) di una parola contenuta in molte celle Excel?

Faccio un esempio. Nelle celle di un foglio ci sono delle celle con queste frasi:

(cella A1) numero porte patinate	(cella B1) numero finestre finitura patinata	(cella C1) finestre patinate
(cella A2) finitura patinata colorata	(cella B2) porte patinate scorrevoli	(cella C2) porte patinate battente
(...) totale porte e finestre	(...) totale porte	(...) totale finestre
(...)	(...)	(...)
(...)	(...)	(...)

Quale comando (se con CTRL+SHIFT+S, quali opzioni settare) mi può trasformare la tabella così?

(cella A1) numero porte patinate	(cella B1) numero finestre finitura patinata	(cella C1) finestre patinate
(cella A2) finitura patinata colorata	(cella B2) porte patinate scorrevoli	(cella C2) porte patinate battente
(...) totale porte e <br>finestre	(...) totale porte	(...) totale finestre

Se effettuo Trova/Sostituisci, inserisco la parola "porte" e cambio colore (qui mi sono limitato a grassetto e corsivo, ma il mio intento è cambiare anche il colore) la sostituzione avviene, ma la nuova formattazione si applica all'intera cella.

Come limitare ad una sola parola, senza alterare le altre proprietà della cella, come allineamento, colore sfondo, formattazione contenuto (numero, generale, testo, data/ora, ...), ecc ecc ...

Ciao Sebastiano,

Prova qualcosa del genere:

Alt-F11 per aprire l'editor di VBA

Alt-IM per inserire un nuovo modulo di codice

Nel nuovo modulo vuoto, incolla il seguente codice:

'==========>>

Option Explicit

'---------->>

Public Sub Tester()

    Dim WB As Workbook

    Dim SH As Worksheet

    Dim Rng As Range, Rng2 As Range, Rng3 As Range, RngOut As Range

    Dim rCell As Range

    Dim sStr As String

    Dim iPos As Long, jPos As Long, iLen As Long, jlen As Long

    Const aStr As String = "porte"                                     '<<==== Modifica

    Const bStr As String = "finestre"                                 '<<==== Modifica

    Set WB = ActiveWorkbook            

    Set SH = WB.Sheets("Foglio2")                                    '<<==== Modifica

    Set Rng = SH.Range("A1:D100")                                 '<<==== Modifica

    On Error Resume Next

    Set Rng2 = Rng.SpecialCells(xlCellTypeConstants, 2)

    Set Rng3 = Rng.SpecialCells(xlCellTypeFormulas, 2)

    On Error GoTo 0

    If Not Rng2 Is Nothing Then

        If Not Rng3 Is Nothing Then

            Set RngOut = Union(Rng2, Rng3)

        Else

            Set RngOut = Rng2

        End If

    Else

        If Not Rng3 Is Nothing Then

            Set RngOut = Rng3

        Else

            Exit Sub

        End If

    End If

    iLen = Len(aStr)

    jlen = Len(bStr)

    For Each rCell In Rng.Cells

        sStr = rCell.Value

        iPos = InStr(1, sStr, aStr, vbTextCompare)

        jPos = InStr(1, sStr, bStr, vbTextCompare)

        If CBool(iPos) Then

            rCell.Characters(iPos, iLen).Font.Bold = True

        End If

        If CBool(jPos) Then

            rCell.Characters(jPos, jlen).Font.Underline = True

        End If

    Next rCell

End Sub

'<<==========

Alt-Q per chiudere l'editor di VBA e tornare a Excel.

Alt-F8 per aprire la finestrina macro

Seleziona Tester | Esegui

===

Regards,

Norman