Record Formatting

nervespy

New Member
Joined
Dec 21, 2004
Messages
2
Please can you help?

Can anyone tell me the VB code to look at a cell and then work out the formatting properties of that cell and then store that value in another cell in the workbook?

I.e. if a cell is formatted as “£” this will then be referenced in another cell in the workbook as “Pounds”

OR

If a cell is formatted as “%” this will be reference in another cell in the workbook as “Percentage”

This will allow me to work out for a given cell what the formatting properties of that cell are and how they are set?

Thanks you for your help,

Tim Marshall
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This may help it lists all cell formatting in a message box:

Sub myCDType()
'Cell type(Data, Format & Formula test), of single cell in MsgBox.
'Load code in "Module."
'Best if run by Hot-key(Macros-Macro-Options).
'
'Test for formula in cell.
Dim myF As String
Dim myFormula As String
If Selection.HasFormula = True Then
myFormula = Selection.Formula
myF = " and contains a [Formula: " & myFormula & "]."
Else
myF = " and contains [No Formula]."
End If
'Test Alignment of selection?
If Selection.HorizontalAlignment = xlGeneral Then X = "General" Else
If Selection.HorizontalAlignment = xlLeft Then X = "Left" Else
If Selection.HorizontalAlignment = xlCenter Then X = "Center" Else
If Selection.HorizontalAlignment = xlRight Then X = "Right" Else
If Selection.HorizontalAlignment = xlFill Then X = "Fill" Else
If Selection.HorizontalAlignment = xlJustify Then X = "Justify" Else
If Selection.HorizontalAlignment = xlCenterAcrossSelection Then X = "Center Across Selection" Else
If Selection.HorizontalAlignment = xlDistributed Then X = "Distributed" Else
If Selection.VerticalAlignment = xlTop Then Y = "Top" Else
If Selection.VerticalAlignment = xlCenter Then Y = "Center" Else
If Selection.VerticalAlignment = xlBottom Then Y = "Bottom" Else
If Selection.VerticalAlignment = xlJustify Then Y = "Justify" Else
If Selection.VerticalAlignment = xlDistributed Then Y = "Distributed" Else
Select Case True
'Test cell data type & format, return to MsgBox for display.
'Note: For some reason the case-order is important!
'
'Cell is Blank, Cell Format & Formula?
Case IsEmpty(Selection): MsgBox "The Cell Data is: [Blank] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Text, Cell Format & Formula?
Case Application.IsText(Selection): MsgBox "The Cell Data is: [Text] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Date, Cell Format & Formula?
Case IsDate(Selection): MsgBox "The Cell Data is a: [Date] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Time, Cell Format & Formula?
Case InStr(1, Selection.Text, ":") <> 0: MsgBox "The Cell Data is a: [Time] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Logical, Cell Format & Formula?
Case Application.IsLogical(Selection): MsgBox "The Cell Data is: [Logical] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Error, Cell Format & Formula?
Case Application.IsErr(Selection): MsgBox "The Cell Data is an: [Error] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
'Cell is Value, Cell Format & Formula?
Case IsNumeric(Selection): MsgBox "The Cell Data is a: [Value] with a [" _
& Selection.NumberFormat & "] Format" & myF & Chr(13) & Chr(13) _
& "The Column is : [" & Selection.ColumnWidth & "] wide" _
& " and the Row is: [" & Selection.RowHeight & "] high!" & Chr(13) & Chr(13) _
& "Horizontal Alignment is: [" & X & "] and the Vertical Alignment is: [" & Y & "]."
End Select
End Sub


Sub CellFormula()
Dim myFormula As String
'Shows cell formula if it has one!

If Selection.HasFormula = True Then
myFormula = Selection.Formula
MsgBox myFormula
Else
MsgBox "Cell has no Formula!"
End If

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,477
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top