Getting cells to identify their format settings

[Mike Welch]

I've found no way to do this and hope someone can help. I want to be able to open a worksheet, select a range of cells (B2:J35) and run some sort of code that will: 1) delete any data present and 2) place in EACH CELL that cells current format settings. (This will be done on a copy of the original as not all fields are filled out on every report and I can't destroy the data.)

When the code finishes, I would want to see no data in the cells but would want to see something akin to:

<TABLE style="WIDTH: 279pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=372 x:str><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" span=3 width=124><TBODY><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=40 width=124>Arial 10 Green
Bold


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Comic 8 Blue
Bold Italic


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Arial 10 Black
Bold YellowFill


</TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=40>MS Sans Serif Red</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Arial 10 Black</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>MS Sans Serif Red</TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=40 width=124>Arial 10 Black</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Arial 10 Black</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Arial 10 Black</TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=40 width=124>Comic 8 Blue
Bold Italic


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Arial 10 Black</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Arial 10 Green
Bold


</TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=40 width=124>Arial 10 Black</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Comic 8 Blue
Bold Italic


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Arial 10 Black</TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 30pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=40 width=124>Arial 10 Black
Bold YellowFill


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Wingdings2 18 Red
LtBluFill


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=124>Verdana 20 Red
Bold BrghtGrnFill


</TD></TR></TBODY></TABLE>

The fonts are (fortunately) limited to 12 different ones and no other ones can be used. Sizes can not exceed 20 points and colors must be on the "standard" pallet. Otherwise the cells come in formatted based upon the data in them (unless somebody screws up).

It would be great if the format info was in the actual font/color etc. (like below) but since I have no clue on even how to get the information to begin with, I'm not holding my breath on getting anything other than the format -- if that can even be done. We're stuck with Excel 2003 and WinXP Pro SP3 as a common platform, so 2007 tricks won't work.

<TABLE style="WIDTH: 279pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=372 x:str><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" span=3 width=124><TBODY><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 30pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=40 width=124>Arial 10 Green
Bold


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=124>Comic 8 Blue
Bold Italic


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 93pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=124>Arial 10 Black
Bold YellowFill


</TD></TR></TBODY></TABLE>

The one thing I already know will be needed (or will make life easier) is if all cells in the range are formatted to the same height and width.

Does anyone have ANY idea how this could work?? (It's a bad system but I have to live with it for awhile...)

Mike

 

[shg]

The code doesn't change whatever fill is in the cell.

Sub x()
    Dim cell As Range
    Dim s As String
 
    For Each cell In Selection.Cells
        With cell.Font
            s = .Name
            s = s & " " & .Size
            If .Bold Then s = s & " " & "Bold"
            If .Italic Then s = s & " " & "Italic"
        End With
        cell.Value = s
    Next cell
End Sub

 

[mikerickson]

Something like

Dim oneCell as Range
Dim formatStr as String

For Each oneCell in Range("A1:C4")
    With oneCell
        With .Font
            formatStr = "Font " & .Name
            formatStr = formatStr &  " size: " & .Size
            If .Bold then formatStr = formatStr & " bold"
         End With
     .Value = formatStr
     End With
Next

The object browser can give you the properties to check.