Mastrar formatos

mdefarias

27-09-2013 14:30:21

bom dia!
gostaria de gerar uma Macro para que eu possa ao clicar em uma célula me mostre
os formatos da mesma, tipo (formato número, tipo de borda, fonte etc.)

abraços

Marcos

vbalexandre

13-10-2013 00:45:28

Boa noite!!

Consegue editar ....para sua necessidade :?:

Código: Selecionar todos

Sub myCDType()
'Cell type(Data, Format, Formula test & Font), of single cell in MsgBox.
'Load code into "Standard Module."
'Best if run by Hot-key(Macros-Macro-Options).
'
'Test for formula in cell.
Dim myF As String
Dim myFColor
Dim myFCNum

On Error GoTo myErr
myFCNum = Selection.Font.ColorIndex ' Initialize variable.
Select Case myFCNum ' Evaluate Number.
Case 0
myFColor = "0: Automatic Color"
Case 1
myFColor = "1: Black"
Case 2
myFColor = "2: White"
Case 3
myFColor = "3: Red"
Case 4
myFColor = "4: Bright Green"
Case 5
myFColor = "5: Blue"
Case 6
myFColor = "6: Yellow"
Case 7
myFColor = "7: Pink"
Case 8
myFColor = "8: Turquoise"
Case 9
myFColor = "9: Dark Red"
Case 10
myFColor = "10: Green"
Case 11
myFColor = "11: Dark Blue"
Case 12
myFColor = "12: Dark Yellow"
Case 13
myFColor = "13: Violet"
Case 14
myFColor = "14: Teal"
Case 15
myFColor = "15: Gray-25%"
Case 16
myFColor = "16: Gray-50%"
Case 33
myFColor = "33: Sky Blue"
Case 34
myFColor = "34: Light Turquoise"
Case 35
myFColor = "35: Light Green"
Case 36
myFColor = "36: Light Yellow"
Case 37
myFColor = "37: Pale Blue"
Case 38
myFColor = "38: Rose"
Case 39
myFColor = "39: Lavender"
Case 40
myFColor = "40: Tan"
Case 41
myFColor = "41: Light Blue"
Case 42
myFColor = "42: Aqua"
Case 43
myFColor = "43: Lime"
Case 44
myFColor = "44: Gold"
Case 45
myFColor = "45: Light Orange"
Case 46
myFColor = "46: Orange"
Case 47
myFColor = "47: Blue-Gray"
Case 48
myFColor = "48: Gray-40%"
Case 49
myFColor = "49: Dark Teal"
Case 50
myFColor = "50: Sea Green"
Case 51
myFColor = "51: Dark Green"
Case 52
myFColor = "52: Olive Green"
Case 53
myFColor = "53: Brown"
Case 54
myFColor = "54: Plum"
Case 55
myFColor = "55: Indigo"
Case 56
myFColor = "56: Gray-80%"

Case Else ' Other values.
myFColor = myFCNum & ": Other-Custom-Automatic Color"
End Select

If Selection.HasFormula = True Then myF = " and contains a [Formula]." Else _
myF = " and contains [No Formula]."
'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 & "]." _
& Chr(13) & Chr(13) & "The font is: [" & Selection.Font.Name & "], size [" _
& Selection.Font.Size & "], style [" & Selection.Font.FontStyle & _
"] and color [" & myFColor & "].", Title:="Cell Address: " & Selection.Address & ", Properties!"
'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 & "]." _
& Chr(13) & Chr(13) & "The font is: [" & Selection.Font.Name & "], size [" _
& Selection.Font.Size & "], style [" & Selection.Font.FontStyle & _
"] and color [" & myFColor & "].", Title:="Cell Address: " & Selection.Address & ", Properties!"
'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 & "]." _
& Chr(13) & Chr(13) & "The font is: [" & Selection.Font.Name & "], size [" _
& Selection.Font.Size & "], style [" & Selection.Font.FontStyle & _
"] and color [" & myFColor & "].", Title:="Cell Address: " & Selection.Address & ", Properties!"
'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 & "]." _
& Chr(13) & Chr(13) & "The font is: [" & Selection.Font.Name & "], size [" _
& Selection.Font.Size & "], style [" & Selection.Font.FontStyle & _
"] and color [" & myFColor & "].", Title:="Cell Address: " & Selection.Address & ", Properties!"
'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 & "]." _
& Chr(13) & Chr(13) & "The font is: [" & Selection.Font.Name & "], size [" _
& Selection.Font.Size & "], style [" & Selection.Font.FontStyle & _
"] and color [" & myFColor & "].", Title:="Cell Address: " & Selection.Address & ", Properties!"
'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 & "]." _
& Chr(13) & Chr(13) & "The font is: [" & Selection.Font.Name & "], size [" _
& Selection.Font.Size & "], style [" & Selection.Font.FontStyle & _
"] and color [" & myFColor & "].", Title:="Cell Address: " & Selection.Address & ", Properties!"
'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 & "]." _
& Chr(13) & Chr(13) & "The font is: [" & Selection.Font.Name & "], size [" _
& Selection.Font.Size & "], style [" & Selection.Font.FontStyle & _
"] and color [" & myFColor & "].", Title:="Cell Address: " & Selection.Address & ", Properties!"
End Select
End
myErr:
MsgBox "Select One Cell Only!"
End Sub