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


"MikeR" wrote in message
om...
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,

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

--
sb
"MikeR" wrote in message
om...
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.

"steve" wrote in message ...
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

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!!

Instead of capturing the ActiveCell's .Value property try getting its .Text
property.

--
Jim Rech
Excel MVP

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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

pini35 wrote in message ...
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.


Cheers pini35, that is exactly what I wanted.... just needed the right syntax.