Extract a cells format and apply to a variable

  • Thread starter Thread starter MikeR
  • Start date Start date
M

MikeR

I need to extract an activecells format and apply that format to a
variable.
I am using the activecell value and placing it alongside a checkbox as
its text. At a later date I am extracting this text value as a search
parameter. Unfortunately it looses format profile and I can't search
on dates or currency.
If I had the format stored I could condition the variable prior to
using it in a search..... any help appreciated.
 
Mike,

Excel doesn't search on format - it searches on value only.

You don't say where you are pasting the value - into a cell, or into
a textbox or other object.

You can format the cell by copying and using pastespecial formats, and
you can search by using the value of the cell - if you are using a
cell.

If you are using a text box, you can use the Activecell.Text to get a
formatted string to paste into the text box. But then you should
search after using the CDbl function to extract the value from the
formatted text string.

HTH,
Bernie
 
Mike,

You'll probably have to assign a variable to each of the many formatting
options. Below is code assigning some of the formatting.

============================================
Code examples to set variables:
HA = Activecell.HorizontalAlignment
FS = Activecell.FontStyle
NF = Activecell.NumberFormat

I think these should be dimmed as String.
============================================

Selection.NumberFormat = "#,##0.00"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "@Batang"
.FontStyle = "Italic"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDashDotDot
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
 
If I'm right in what I think you want to do, you can try using:

*ActiveCell.NumberFormat*

this will give you the format of the current cell. For example a cell
that is formatted as Text will return "@".

General cells will return "General".
Time and Date cells will return the time or date format you selected
etc.

You can format the cells, then run some code to record all the formats
you require.
 
If I'm right in what I think you want to do, you can try using:

*ActiveCell.NumberFormat*

this will give you the format of the current cell. For example a cell
that is formatted as Text will return the "@".

General cells will return "General".
Time and Date cells will return the time or date format you selected
etc.

You can format your cells, then run some code to record all the formats
you require.
 
steve said:
Mike,

You'll probably have to assign a variable to each of the many formatting
options. Below is code assigning some of the formatting.

============================================
Code examples to set variables:
HA = Activecell.HorizontalAlignment
FS = Activecell.FontStyle
NF = Activecell.NumberFormat
Click to expand...

Thanks Bernie,Jim and Steve for your replies.....

Jim gave me the clue I needed....
FormatType = ActiveCell.Offset(1, 0).NumberFormat <-----!!!!!!! this did it

rest of code......

If FormatType <> "General" Then
FindMe = Format(Names(j), FormatType)
Else
FindMe = Names(j)
End If

Continue with code.....
I can now use my filter to "find" any format type I am likely to encounter!!
 
pini35 said:
If I'm right in what I think you want to do, you can try using:

*ActiveCell.NumberFormat*

this will give you the format of the current cell. For example a cell
that is formatted as Text will return the "@".

General cells will return "General".
Time and Date cells will return the time or date format you selected
etc.

You can format your cells, then run some code to record all the formats
you require.
Click to expand...
Cheers pini35, that is exactly what I wanted.... just needed the right syntax.
 
Back
Top