Excel conditional replacing

Question

Hi All,

I'd like to find cells with a particular interior colour in a range and set their colorindex to "No fill (-4142)" and delete their content (replace them by an empty string ("").

This is my code:

Sub Macro8()

    Application.FindFormat.Interior.Color = 14277081   'White, Background 1, Darker 15%

    Application.ReplaceFormat.Interior.ColorIndex = -4142

    Range("A1:AG7").Replace What:="*", Replacement:="", LookAt:=xlPart, _

        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _

        ReplaceFormat:=True

End Sub

Changing the interior colour works but replacing the content doesn't work. I tried it also with LookAt:=xlWhole but it failed, too.

Could you help, please?

Thanks,

István

Answer 1

Try the following

Sub Macro2()

    Dim ws As Worksheet

    Dim rngToFind As Range      'Individual cell to find

    Dim rngFormat As Range      'Used to save the range of all cells that meet the Find

    Dim strFirstAddr As String  'Address of first found cell in the range that meets the Find criteria

    Set rngFormat = Nothing     'Just good programming to ensure that the variable does not already contain a range

    Set ws = Worksheets("Sheet1")   'Edit "Sheet1" to your worksheet name

    '*********************************************************************

    'Between asterisk lines sets the required criteria for the FindFormat and ReplaceFormat

    Application.FindFormat.Clear

    Application.FindFormat.Interior.Color = 14277081

    Application.ReplaceFormat.Interior.ColorIndex = -4142

    Application.FindFormat.Locked = True

    Application.FindFormat.FormulaHidden = False

    '*********************************************************************

    'Loop through the range and find all cells that meet the Find and FindFormat Criteria

    With ws.Range("A1:AG7")

        Set rngToFind = .Find(What:="*", _

                        SearchFormat:=True)

        If Not rngToFind Is Nothing Then

            strFirstAddr = rngToFind.Address

        End If

        Do

            If rngFormat Is Nothing Then

                Set rngFormat = rngToFind

            Else

                Set rngFormat = Union(rngFormat, rngToFind)

            End If

            Set rngToFind = .FindNext(rngToFind)

            If rngToFind Is Nothing Then Exit Do

        Loop While rngToFind.Address <> strFirstAddr

        'Replace cells in the range that contain any value with the Replaceformat

        'Note that following code is still within the With range.

        If Not rngFormat Is Nothing Then

            .Replace What:="*", _

                    Replacement:="", _

                    LookAt:=xlPart, _

                    SearchOrder:=xlByRows, _

                    MatchCase:=False, _

                    SearchFormat:=True, _

                    ReplaceFormat:=True

        End If

    End With

End Sub