PDA

View Full Version : [SOLVED] Replace Formats



mdmackillop
07-09-2005, 12:35 AM
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
07-09-2005, 02:10 AM
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
07-09-2005, 02:24 AM
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
07-09-2005, 02:54 AM
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!