Macro to list all the format of a cell

[Guest]

Is there a way to find out the all formats of a cell? or all cells have a
particular format.

Thanks in advance

Ashish

 

[Dave Peterson]

This'll use the activecell's format and search for cells with that format:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myKeyCellFormat As String
Dim rptWks As Worksheet
Dim curWks As Worksheet
Dim oRow As Long

Set curWks = ActiveSheet
myKeyCellFormat = ActiveCell.NumberFormat

Set rptWks = Worksheets.Add

rptWks.Range("a1").Value = "'" & myKeyCellFormat
oRow = 1
For Each myCell In curWks.UsedRange
If myCell.NumberFormat = myKeyCellFormat Then
oRow = oRow + 1
rptWks.Cells(oRow, "A").Value = myCell.Address
End If
Next myCell

End Sub

This just picks up the number format. If you mean the fill color, font color,
border (and border styles), it would need to check them, too.