find cell with different font

[bcordoba]

Problem: I need to find cells that do not have either the Arial or Symbol fonts (as our printer driver on the server does not like any other font). This can work for the whole workbook or per Sheet, either way.
Then I will manually change the fonts.
Alternatively we could change the font that is not "arial or Symbol", to Arial, directly! :o)

Suggestion: I am thinking of having a routine that searches through the cells and changes the color of the font (to orange) on those cells that contain a different font than Arial and Symbol.

I have basic knowledge of how to use VBA.

Thank you very much for the help!

Benoit

[abduljaleel.mca]

This file will help you out....

[bcordoba]

Thank you very much! The routine works but only if the font that is not arial or symbol is in column A; how to modify the routine so it looks in every column?

Benoit

[JOHN H. DAVIS]

Another:

Please Login or Register  to view this content.

[bcordoba]

Thank you John, that works almost perfectly! The cells that have symbols in them also turned to Arial... something is odd because the code made sense... do you have an idea.

Also I have cells in my spreadsheet that have multiple fonts; hopefully the routine will not change the whole cell to a single font... we'll see!

:o)

[bcordoba]

I changed the "or" to "and" and now it works!

Thank you very much!!!

Benoît

Final code:

Sub bcordoba()
Dim rcell As Range
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets

ws.Activate

For Each rcell In ws.UsedRange

If rcell.Font.Name <> "Arial" and rcell.Font.Name <> "Symbol" Then

rcell.Font.Name = "Arial"

End If

Next rcell

Next ws

Application.ScreenUpdating = True
End Sub

[bcordoba]

John,

Again thank you.

The code works for changing the entire cell to a font; is there a way to change the portion of the content of a cell that are not font Arial or Symbol? I mix fonts in some cells and they do not change the words in non-arial-or-symbol if there are some words in arial or symbol fonts in those cells.

Benoit

[JOHN H. DAVIS]

You're are welcome. The code seems to work for cells which have entire cells with one font and skips over the mixed code. If you need it to deal with Arial and Symbol letters in a mixed font cell, I'm afraid I can't help you their. I wouldn't know where to begin.

[bcordoba]

Thank you John. Mixed font cells are easier to spot so I will check them. I really, really appreciate your help with this problem.



Benoît

[JOHN H. DAVIS]

Again welcome. Glad to help out. Not sure if anyone else can help you with the mixed font cells. But if you consider this thread solved please adhere too Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.