Return Format

[HWL]

Is there a simple one-liner code to return that format of a cell (using VBA)
Like?
range.("b2").format

I'd like to be able to return the color code and the text-type (such as value, text, general)

Thanks

 

[Smitty]

Take a look at the CellFormat object in the VBA helpfile.

There's also the CELL function for worksheets.

HTH,

 

[VoG]

There are several properties that may be of interest

.NumberFormat

.Interior.ColorIndex

.Interior.Color

.TintAndShade

.Font.ColorIndex

and so on.

 

[HWL]

There are several properties that may be of interest

.NumberFormat

.Interior.ColorIndex

.Interior.Color

.TintAndShade

.Font.ColorIndex

and so on.

These are good thanks! Is there a .format prop? (not numberformat) and perhaps a place I can see all the possible cell formats as a list like you have here with these few?

 

[VoG]

There isn't a .Format property.

For .NumberFormat see http://www.ozgrid.com/Excel/CustomFormats.htm

 

[HWL]

There isn't a .Format property.

For .NumberFormat see http://www.ozgrid.com/Excel/CustomFormats.htm

Thanks Peter, this helped much.

 

[HWL]

Thanks Peter, this helped much.

One more question, is there a way to simply copy all cell formats to another cell (using vba) instead of setting each property?

 

[Datsmart]

This VBA code will copy only the Format of A1 to B1.

Sub TestFormat()
    Range("A1").Copy
    Range("B1").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

 

[HWL]

Workbook ref

Okay this one is annoying.

I have a call to another macro but I can't seem to reference a workbook in the second macro.

In the first macro I have:
Set Newbook = Workbooks.Add
ThisWorkbook.Sheets(1).Range("R18") = Newbook.Name


In the second macro I have:
Set Newbook = Workbooks(ThisWorkbook.Sheets(1).Range("R18"))

What am I doing wrong that I can refer to this new workbook in the second macro?