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