Conditional format: can’t define text font (all?)

Topic

New Reply

Hey, members of the lounge,
this isn’t critical, but it should be nice…
Does anyone know about certain conditions where you can’t conditional format the text font in cells? In attachment a sample: I want to change the text font (e.g. 6 ipv 10) when a value is 0. The condition is properly defined and I can change a number of layout properties but NOT the font.
TIA!!
Hans

Reply | Quote

Replies

According to the dialog, you can only change the font style, underline, color and strikethrough. In other words, you cannot change the font name (Arial, Tahoma, …) or font size (9, 10, 12, …)

Reply | Quote

OK, Hans,
thanks & sorry, I should have been a bit more careful before throwing out this post. You know, I really seemed to remember that once, in some spreadsheet, I had changed the font with conditional formatting too, so it seemed pretty weird that it wasn’t possible here, without any apparent reason. In the meanwhile, I think I’ve found back the file where I thought I did it and… I’ve done it manually at that time . So, it just really isn’t possible at all, nowhere (checked it out in XP and it was the same over there… (Too bad, which makes me wonder though, by the way, to those wonder guys at Redmond: why not?)

SORRY to have waisted your valuable time but thanks for making me realise my mistake. This saves me some useless searching time!

Hasse

Reply | Quote

OK, Hans,
thanks & sorry, I should have been a bit more careful before throwing out this post. You know, I really seemed to remember that once, in some spreadsheet, I had changed the font with conditional formatting too, so it seemed pretty weird that it wasn’t possible here, without any apparent reason. In the meanwhile, I think I’ve found back the file where I thought I did it and… I’ve done it manually at that time . So, it just really isn’t possible at all, nowhere (checked it out in XP and it was the same over there… (Too bad, which makes me wonder though, by the way, to those wonder guys at Redmond: why not?)

SORRY to have waisted your valuable time but thanks for making me realise my mistake. This saves me some useless searching time!

Hasse

Reply | Quote

According to the dialog, you can only change the font style, underline, color and strikethrough. In other words, you cannot change the font name (Arial, Tahoma, …) or font size (9, 10, 12, …)

Reply | Quote

You can accomplish this using the Worksheet Change event routine:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ocell As Range
    If Not Intersect(Target, ActiveSheet.Range("B2:D5")) Is Nothing Then
        For Each ocell In Intersect(Target, ActiveSheet.Range("B2:D5"))
            If ocell.Value = 0 Then
                ocell.Font.Name = "Wingdings"
            Else
                ocell.Font.Name = "BAZOOKA"
            End If
        Next ocell
    End If
End Sub

Reply | Quote

Wow, now you made me dare using such Change event routine the first time (even though I know some VBA), as this seemed fun…
I changed the “B2:D5” in your example into the area on which I want to apply this high tech conditional formatting & customised the Font properties.
And it works !
Now I’ll probably ask some stupid VB-question but… as I always like to know what I’m doing: what is that ‘Target’ variable passed to this routine? Can I just leave it there in peace without needing it to do the job? What does it do? Is it the current cell?

Reply | Quote

Wow, now you made me dare using such Change event routine the first time (even though I know some VBA), as this seemed fun…
I changed the “B2:D5” in your example into the area on which I want to apply this high tech conditional formatting & customised the Font properties.
And it works !
Now I’ll probably ask some stupid VB-question but… as I always like to know what I’m doing: what is that ‘Target’ variable passed to this routine? Can I just leave it there in peace without needing it to do the job? What does it do? Is it the current cell?

Reply | Quote

The Worksheet_Change event occurs if one or more cells in the worksheet are changed by the user (or by an external link). The Target argument is the range of cells that have changed. Legare’s code checks whether this range intersects with the cells you are interested in – if it doesn’t, you don’t want to do anything, and if it does, you want to format the cells in the intersection.

Reply | Quote

Thanks!

Reply | Quote

Thanks!

Reply | Quote

The Worksheet_Change event occurs if one or more cells in the worksheet are changed by the user (or by an external link). The Target argument is the range of cells that have changed. Legare’s code checks whether this range intersects with the cells you are interested in – if it doesn’t, you don’t want to do anything, and if it does, you want to format the cells in the intersection.

Reply | Quote

You can accomplish this using the Worksheet Change event routine:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ocell As Range
    If Not Intersect(Target, ActiveSheet.Range("B2:D5")) Is Nothing Then
        For Each ocell In Intersect(Target, ActiveSheet.Range("B2:D5"))
            If ocell.Value = 0 Then
                ocell.Font.Name = "Wingdings"
            Else
                ocell.Font.Name = "BAZOOKA"
            End If
        Next ocell
    End If
End Sub

Reply | Quote