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


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


.Font.Name = "Tahoma"


= 34

End With

'Set the Replace criteria for

the Replace Formats

With oCellReplaceFormat


.Font.Name = "Arial"


= 35

End With

'Perform the replace

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

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

'Reset the Find and Replace formats



End Sub

0 0

Post a comment