Changing format of cells

Hello! I have have a macro that seaches several spreadsheets for certain
cells. The problem is that some of the spreasheets has cells with strange
formatting e.g. some customized formats. If a cell that I am searching for
has one of these formats it cannot be found. The cells are identfied by their
content that is unique in the sense that only one cell on a certain
spreadsheet has e.g. the conent "FX TRADE". Other cells may share parts of
the cell contents e.g. "FX POS" but they are not to be found. I search like
this:

Worksheets(worksheetName).Cells.Find(Array(i), LookIn:=xlValues,
LookAt:=xlWhole)

This works except when the cells that I am looking for has strange
formatting. I tried recording a macro that changes the formatting of a
spreadsheet and applied it to my macro and it works. However some cells on
the spreadsheet has a ceratin formatting e.g. "Date" and these are destroyed
when setting a new format (I used "General"). Therefore I want to search the
spreadsheet for cells that have "strange formatting" and if they have that I
want to change the cells formatting to general. I tried writing code for this
but without success:

Sheets("FX").Select
Cells.Select
If Selection.NumberFormat = "Selection.NumberFormat =
"[$-41D]dd/mmm;@" Then

Selection.Format = "General"

End If

This does not work (I am not surprised myself). How do you write the code?
Please I really need help with this! Thank you!


Selection.NumberFormat = "General"

Selection.NumberFormat = "[$-41D]dd/mmm;@"

Hi Victor,

> This works except when the cells that I am looking for has strange
> formatting.

What is the strange formatting?

Give an eample of cell content and formatting that is not found by your
search.


---
Regards,
Norman


"Viktor Ygdorff" <ViktorYgdorff@discussions.microsoft.com> wrote in message
news:38A60C21-58EB-4BE2-BA21-0796B936C7C7@microsoft.com...
> Hello! I have have a macro that seaches several spreadsheets for certain
> cells. The problem is that some of the spreasheets has cells with strange
> formatting e.g. some customized formats. If a cell that I am searching for
> has one of these formats it cannot be found. The cells are identfied by
> their
> content that is unique in the sense that only one cell on a certain
> spreadsheet has e.g. the conent "FX TRADE". Other cells may share parts of
> the cell contents e.g. "FX POS" but they are not to be found. I search
> like
> this:
>
> Worksheets(worksheetName).Cells.Find(Array(i), LookIn:=xlValues,
> LookAt:=xlWhole)
>
> This works except when the cells that I am looking for has strange
> formatting. I tried recording a macro that changes the formatting of a
> spreadsheet and applied it to my macro and it works. However some cells on
> the spreadsheet has a ceratin formatting e.g. "Date" and these are
> destroyed
> when setting a new format (I used "General"). Therefore I want to search
> the
> spreadsheet for cells that have "strange formatting" and if they have that
> I
> want to change the cells formatting to general. I tried writing code for
> this
> but without success:
>
> Sheets("FX").Select
> Cells.Select
> If Selection.NumberFormat = "Selection.NumberFormat =
> "[$-41D]dd/mmm;@" Then
>
> Selection.Format = "General"
>
> End If
>
> This does not work (I am not surprised myself). How do you write the code?
> Please I really need help with this! Thank you!
>
>
> Selection.NumberFormat = "General"
>
> Selection.NumberFormat = "[$-41D]dd/mmm;@"
>

Hello Norman! Here is an exaple of a formatting that disables my search:

Selection.NumberFormat = "[$-41D]dd/mmm;@"

Now the problem is that I have not done the indata and it keeps coming new
indata and someone else (poor soul) is supposed to use my macro without
having to be bothered by changing formats etc.

"Norman Jones" skrev:

> Hi Victor,
>
> > This works except when the cells that I am looking for has strange
> > formatting.
>
> What is the strange formatting?
>
> Give an eample of cell content and formatting that is not found by your
> search.
>
>
> ---
> Regards,
> Norman
>
>
> "Viktor Ygdorff" <ViktorYgdorff@discussions.microsoft.com> wrote in message
> news:38A60C21-58EB-4BE2-BA21-0796B936C7C7@microsoft.com...
> > Hello! I have have a macro that seaches several spreadsheets for certain
> > cells. The problem is that some of the spreasheets has cells with strange
> > formatting e.g. some customized formats. If a cell that I am searching for
> > has one of these formats it cannot be found. The cells are identfied by
> > their
> > content that is unique in the sense that only one cell on a certain
> > spreadsheet has e.g. the conent "FX TRADE". Other cells may share parts of
> > the cell contents e.g. "FX POS" but they are not to be found. I search
> > like
> > this:
> >
> > Worksheets(worksheetName).Cells.Find(Array(i), LookIn:=xlValues,
> > LookAt:=xlWhole)
> >
> > This works except when the cells that I am looking for has strange
> > formatting. I tried recording a macro that changes the formatting of a
> > spreadsheet and applied it to my macro and it works. However some cells on
> > the spreadsheet has a ceratin formatting e.g. "Date" and these are
> > destroyed
> > when setting a new format (I used "General"). Therefore I want to search
> > the
> > spreadsheet for cells that have "strange formatting" and if they have that
> > I
> > want to change the cells formatting to general. I tried writing code for
> > this
> > but without success:
> >
> > Sheets("FX").Select
> > Cells.Select
> > If Selection.NumberFormat = "Selection.NumberFormat =
> > "[$-41D]dd/mmm;@" Then
> >
> > Selection.Format = "General"
> >
> > End If
> >
> > This does not work (I am not surprised myself). How do you write the code?
> > Please I really need help with this! Thank you!
> >
> >
> > Selection.NumberFormat = "General"
> >
> > Selection.NumberFormat = "[$-41D]dd/mmm;@"
> >
>
>
>

Hello again Norman! Sorry I forgot to give an example of cell content. I have
found one instance having the same formatting as in my previous reply i.e.
Selection.NumberFormat = "[$-41D]dd/mmm;@"
and the cell content that I am searching for is "Guld (Ränta)". Thank you
very much for your help Norman!

"Norman Jones" skrev:

> Hi Victor,
>
> > This works except when the cells that I am looking for has strange
> > formatting.
>
> What is the strange formatting?
>
> Give an eample of cell content and formatting that is not found by your
> search.
>
>
> ---
> Regards,
> Norman
>
>
> "Viktor Ygdorff" <ViktorYgdorff@discussions.microsoft.com> wrote in message
> news:38A60C21-58EB-4BE2-BA21-0796B936C7C7@microsoft.com...
> > Hello! I have have a macro that seaches several spreadsheets for certain
> > cells. The problem is that some of the spreasheets has cells with strange
> > formatting e.g. some customized formats. If a cell that I am searching for
> > has one of these formats it cannot be found. The cells are identfied by
> > their
> > content that is unique in the sense that only one cell on a certain
> > spreadsheet has e.g. the conent "FX TRADE". Other cells may share parts of
> > the cell contents e.g. "FX POS" but they are not to be found. I search
> > like
> > this:
> >
> > Worksheets(worksheetName).Cells.Find(Array(i), LookIn:=xlValues,
> > LookAt:=xlWhole)
> >
> > This works except when the cells that I am looking for has strange
> > formatting. I tried recording a macro that changes the formatting of a
> > spreadsheet and applied it to my macro and it works. However some cells on
> > the spreadsheet has a ceratin formatting e.g. "Date" and these are
> > destroyed
> > when setting a new format (I used "General"). Therefore I want to search
> > the
> > spreadsheet for cells that have "strange formatting" and if they have that
> > I
> > want to change the cells formatting to general. I tried writing code for
> > this
> > but without success:
> >
> > Sheets("FX").Select
> > Cells.Select
> > If Selection.NumberFormat = "Selection.NumberFormat =
> > "[$-41D]dd/mmm;@" Then
> >
> > Selection.Format = "General"
> >
> > End If
> >
> > This does not work (I am not surprised myself). How do you write the code?
> > Please I really need help with this! Thank you!
> >
> >
> > Selection.NumberFormat = "General"
> >
> > Selection.NumberFormat = "[$-41D]dd/mmm;@"
> >
>
>
>