CODEWITHSUNDEEP
excel
Srihari
Srihari

Reputation: 31

Change the color of a text with in all the cells of an excel sheet

Is there a way to change the color of the text in ALL of the cells in an Excel sheet? Something like finding a text and change the forecolor of the searched text only for the cells of the Excel sheet.

Upvotes: 2

Views: 4376

Answers (1)

ashleedawg
ashleedawg

Reputation: 21639

Change the color of all the cells of an excel sheet:

As an example:

  1. Select the entire worksheet or a range of cells.
  2. On the Home tab choose Conditional Formatting
  3. Click New Rule...
  4. Click Use a formula to determine which cells to format
  5. Under Format cells where this value is true enter formula: =(LEN($A$1)>0)
  6. Click Format and go to the Fill tab
  7. Choose a fill color. Click OK, OK.

Now if cell A1 has any value in it, the entire range selected in step 1 will change color. You can specify different cell ranges, criteria, or formatting, as necessary. (For example, text color instead of fill color)

Edit #1:

Re: Find & Replace to change color of part of a cell

Find & Replace can search for, or replace, cell formatting, but the replacement formatting affects the entire cell.

Attempt at Search & Replace format in part cell

Result: (whole cell changed)

Result

Edit #2a:

You said "no VBA" but for the sake of sharing possible alternative solutions, here is how this could be accomplished with VBA. This method loops through all cells in ActiveSheet.UsedRange:

Sub SearchReplace_Color_PartialCell()

    Const textToChange = "cat"
    Const newColor = vbRed
    Dim c As Range

    'loop throgh all cells that have data
    For Each c In ActiveSheet.UsedRange.Cells
        If InStr(c.Value, textToChange) > 0 Then 'if text exists in cell
            ' then change the color of that text
            c.Characters(InStr(c.Value, textToChange), Len(textToChange)).Font.Color = newColor 

        End If

    Next c

End Sub

When run on 10,000 cells, each with varying length strings, all with the word "cat" in the middle, this method ran in 2.6797 seconds.

Edit #2b:

Another VBA solution, using .Find and .FindNext to loop through cells with data:

Sub FindReplace_Color_PartialCell()

    Const textToChange = "cat"
    Const newColor = vbRed

    Dim c As Range, firstAddress As String

    With ActiveSheet.Cells
         Set c = .Find(textToChange, LookIn:=xlValues)
         If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Characters(InStr(c.Value, textToChange), Len(textToChange)).Font.Color = vbGreen
                Set c = .FindNext(c)
            If c Is Nothing Then
                GoTo DoneFinding
            End If
            Loop While c.Address <> firstAddress
          End If
  DoneFinding:
    End With

End Sub

When run on 10,000 cells each with varying length strings, all with the word "cat" in the middle, this method ran in 8.7021 seconds.

Edit #2c:

Modified to continue searching a cell until no further matches are found (instead of moving to next cell after one replacement):

Sub SearchReplace_Color_PartialCell()
    'modified to catch multiple occurences of search term within the single cell

    Const textToChange = "cat"
    Const newColor = vbGreen
    Dim c As Range 'the cell we're looking at
    Dim pos As Integer 'current position#, where we're looking in the cell (0 = Not Found)
    Dim matches As Integer 'count number of replacements

    For Each c In ActiveSheet.UsedRange.Cells 'loop throgh all cells that have data

        pos = 1

        Do While InStr(pos, c.Value, textToChange) > 0   'loop until no match in cell

            matches = matches + 1

            pos = InStr(pos, c.Value, textToChange)

            c.Characters(InStr(pos, c.Value, textToChange), Len(textToChange)).Font.Color = _
                newColor ' change the color of the text in that position
            pos = pos + 1 'check again, starting 1 letter to the right

        Loop

    Next c

    MsgBox "Replaced " & matches & " occurences of """ & textToChange & """"

End Sub

Upvotes: 4

Related Questions