Replace Formats

[mdmackillop]

Here is some recorded code to change cell colour from light green (35) to Yellow (6). All other formatting comes from the sheet setup and will be deleted in due course. It works when I record it, but not when I run it. The problem seems to lie in the fact that there is no data to find. Any suggestions?

Sub ReplaceFormat()
    Application.FindFormat.Clear
    Application.FindFormat.NumberFormat = "General"
    With Application.FindFormat
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Application.FindFormat.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
    Application.FindFormat.Borders(xlRight).LineStyle = xlNone
    Application.FindFormat.Borders(xlTop).LineStyle = xlNone
    Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
    Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
    Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
    With Application.FindFormat.Interior
        .ColorIndex = 35
        .Pattern = xlSolid
    End With
    Application.FindFormat.Locked = True
    Application.FindFormat.FormulaHidden = False
    Application.ReplaceFormat.Interior.ColorIndex = 6
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

[Bob Phillips]

I recorded this and suitably tarted up it works fine when run independently.

I did notice that my recorded code stuck a Find in but I removed that and it still workled okay.

Apart from the plethora of formats, what is different about mine?

Dim cell As Range
With Application
        With .FindFormat
            .Clear
            .Interior.ColorIndex = 35
        End With
        With .ReplaceFormat
            .Clear
            .Interior.ColorIndex = 19
        End With
    End With
Cells.Replace What:="", _
                  Replacement:="", _
                  LookAt:=xlPart, _
                  SearchOrder:=xlByRows, _
                  MatchCase:=False, _
                  SearchFormat:=True, _
                  ReplaceFormat:=True

[mdmackillop]

Stranger and stranger.
Your code works, and when I pasted in my code from above, it now works! Just put it down to experience I guess.
Thanks for your help.

[Bob Phillips]

Stranger and stranger.

I would hope I am not a stranger by now

Your code works, and when I pasted in my code from above, it now works! Just put it down to experience I guess.
Thanks for your help.

I often find that just airing it makes one (me) go through it a bit more carefully, avoiding whatever mistake I previously made. It's good to talk!