Cell Format Object Example

The following routine searches through the used range in the active worksheet, replacing any cells containing both a Tahoma font and a light blue background with Arial light green background:

Sub ReplaceFormats()

Dim oCellFindFormat As CellFormat

Dim oCellReplaceFormat As

CellFormat

Dim rngReplace As Boolean,

sMessage As String

'Define variables for Find

and Replace formats

Set oCellFindFormat = Application.FindFormat

Set oCellReplaceFormat = Application.ReplaceFormat

'Set the Search criteria for the Find Formats

With oCellFindFormat

.Clear

.Font.Name = "Tahoma"

.Interior.Colorlndex

= 34

End With

'Set the Replace criteria for

the Replace Formats

With oCellReplaceFormat

.Clear

.Font.Name = "Arial"

.Interior.Colorlndex

= 35

End With

'Perform the replace

ActiveSheet.UsedRange.Replace What:="", _

Replacement:^'", SearchFormat:=True, ReplaceFormat:=True

'Reset the Find and Replace formats

oCellFindFormat.Clear

oCellReplaceFormat.Clear

End Sub

0 0

Post a comment