Change font in cell based on value

[louie5117]

Hi everyone, new here as you will be able to tell from the content of the post that will follow.

Basically, I am at the moment doing a simple formula that returns a tick if a value is within a 5% tolerance margin of a certain value. In order to get a tick or cross returned I have used "Webdings 2" font on the particular cell and applied contional formatting so the tick returns green for a tick and red for a cross.

My problem is, if the value hasn't been populated (the figure isn't available) I have the formula returning "N/A".

As the cell is set to webdings 2, the random charecters show up rather than the desired text.

Is there any way of applying a code so that the font will change based on the returned cell value or text?

Hope you can help guys. Sorry if this has already been posted, I did a search!

[Bob Phillips]

Get your formula to handle the NA. What is the formula?

[louie5117]

Hi mate,

Cheers for getting back. I am a little shy to post the formula as it was a quick job written by somebody else! I promise:

=IF(D3="","n/a",IF(D3<=(D2*95%),"O",IF(D3>=(D2*105%),"O","P")))

D2 being the target figure
D3 being the actual performance

I also have simple conditional formatting applied just to apply green text / red text accordingly.

[Bob Phillips]

How about ditching the NA

=IF(D3="","",IF(D3<=(D2*95%),"O",IF(D3>=(D2*105%),"O","P")))

[louie5117]

That is how I had it originally by the management wanted the cell to default to N/A if the data wasn't available at the time we publish the figures.

Damn Webdings! ha!

Cheers though!

[Bob Phillips]

Then you are out of luck as CF cannot set the font.

You could do it with VBA, want that?

[louie5117]

I would be forever in your debt if you could point me in the right direction my friend.

Good bunch of people on this forum!

[Bob Phillips]

I can do better than that.

BTW, I assume you mean Wingdings 2 not WebDings to get checkmarks.



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

With Me.Range(WS_RANGE)

Select Case .Value

Case "O":

.Font.Name = "Wingdings 2"
.Font.ColorIndex = 3
Case "P":

.Font.Name = "Wingdings 2"
.Font.ColorIndex = 10
Case Else:

.Font.Name = "Arial"
.Font.ColorIndex = xlColorIndexAutomatic
End Select
End With

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[louie5117]

Mate you are a superstar! I can't thank you enough for this.

This solves my problem nicely!