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
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