Changing font style and color using VBA



  • 1. From Macro in Excel find cursor location eg.C7
    Whilst running a Macro, I would like find the row value of the cursor. eg. if the cursor is in cell F3 then I would expect the result to be 6
  • 2. Repeatition of the same item
    I want to avoid repeatition of the tags in the rows i am entering. ie.. if i have entered 98TI-107 once it should notify me if i am trying to enter it once again. Thanks
  • 3. Creating automatic listings
    Can I create a list on the first worksheet in a workbook to include a particular cell content in each of of the subsequent worksheets and will automatically include new sheets as they are added to the workbook? Sort of like an index for the workbook. Thanks for any help.
  • 4. Apostrophes in numbers with 16 characters
    1. How I get rid of ' in the beggining of a number (data comes from Access I believe) - it gets confused with text formats 2. Once I try to convert this to number, I lose the last character, renumbered to "0" 3. If I try to multiply by 1 or sum 0 to make it a number, also loses the last character, renumbered to 0. 4. If I try to combine this "renumbered" to 0 number, using RIGHT/MID with the last character removed, it turns back to text once its combined (using &) Thanks

Changing font style and color using VBA

Postby U3BvbmdpZQ » Thu, 01 Oct 2009 23:39:02 GMT

I have been trying (unsuccessfully!) to write some code to change the font 
style and color based on specific criteria.

I have a spreadsheet with input cells in columns A & B and a formula in 
column C to determine the % variance between the 2.  What I want to do is in 
column D return a symbol based on a RAG status (Red, Amber, Green).  I can't 
do this using a formula as the Red and Green symbols are Wingdings and the 
Amber symbol uses Wingdings 3.

So if A1=40 and B1=45 the variance is 11% (as shown in C1)and I'd like this 
to return a particular symbol (Amber triangle), which I believe is:
Value = ""
Font Name = Wingdings 3
Font Color = RGB(255,192,0)

But I'm struggling with the actual code....please don't laugh at my pathetic 
attempts (completely self-taught) but this is what I wrote....

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("G6") <= 0.1 Then
With Range("H6")
.Value = "l"
.Font.Name = "Wingdings"
.Font.Color = RGB(0, 0, 255)
End With

ElseIf Range("G6") <= 0.2 Then
With Range("H6")
.Value = ""
.Font.Name = "Wingdings 3"
.Font.Color = RGB(255, 192, 0)
End With

ElseIf Range("G6") > 0.2 Then
With Range("H6")
.Value = ""
.Font.Name = "Wingdings"
.Font.Color = RGB(255, 0, 0)
End With

End If

It inputs the correct symbol but then Excel completely freezes...what am I 
doing wrong??

Any help greatly appreciated!

RE: Changing font style and color using VBA

Postby THVrZSBN » Fri, 02 Oct 2009 00:15:02 GMT

Since your Change Event macro "changes" the sheet, you need to temporarily 
disable events to prevent an endless loop. Note that the EnableEvents does 
not automatically get reset to "true" at the end of a macro, so you want to 
make sure that is always included at the end.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Range("H6")
If Range("G6") <= 0.1 Then
.Value = "l"
.Font.Name = "Wingdings"
.Font.Color = RGB(0, 0, 255)

ElseIf Range("G6") <= 0.2 Then
.Value = ""
.Font.Name = "Wingdings 3"
.Font.Color = RGB(255, 192, 0)

ElseIf Range("G6") > 0.2 Then
.Value = ""
.Font.Name = "Wingdings"
.Font.Color = RGB(255, 0, 0)

End If
End With
Application.EnableEvents = True
End Sub

Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

RE: Changing font style and color using VBA

Postby U3BvbmdpZQ » Fri, 02 Oct 2009 00:24:01 GMT

You are an absolute star - thank you very much!!!

Re: Changing font style and color using VBA

Postby Gord Dibben » Fri, 02 Oct 2009 04:34:21 GMT


Just an add-on to your advice about disabling/enabling events.

In order to reset events in case of an error you should trap for an error.

On Error Goto stoppit
Application.EnableEvents = False

      code to run

Application.EnableEvents = True
End Sub

Gord Dibben  MS Excel MVP

On Wed, 30 Sep 2009 08:15:02 -0700, Luke M < XXXX@XXXXX.COM >

Similar Threads:

1.change font color, type,or style

hello i was wondering is it possible in visual basic .net to change the
color of certain words or phrases without changing the color of all the
text in the lable or without having to create a new lable specificly
for that word or phrase 


2.Word 2002 SP3 - change font color creates new style - breaking mac


I had slowly put together macros that would take a tab delimited database 
dump -- a list of names and codes and numbers -- off of our mainframe, and 
turn it into a nicely formatted office phone directory.

I did this entirely with styles -- no direct formatting.  But I used 
character styles that only changed font colors -- so I could find a unique 
long string (containing for example a foreign city name and an eleven digit 
number) and paint that green, then find a string of eleven green numbers and 
break it up into four numbers, hyphen, three numbers, hyphen, four numbers.

This broke completely with Word 2002 --  Word 2002 creates a new named style 
if I apply a character style that includes changing the font color of the 
character.   So my subsequent searches don't find the characters, because 
Word has changed their style name and my search doesn't know what they're now 

I suppose I should have used strikeouts or crawling ants or something 
instead of colors.  Now, what to do ....???

Any suggestion welcome -- ideally some way to turn off this new feature in 
Word 2002 and make it leave the style names the way I create them.

I'm not a macro expert; I did this slowly by guess and by gosh, got some 
help from experts who aren't available now -- so I can't just rewrite the 
whole macro.

Any help welcome.

3.Word 2002 SP3 - change font color creates new style - breaking

4.change font color of tasks or color of bars from VBA


How is it possible to change the fontcolor of a task or the color of a bar
from vba?


5.getting font name, style, and color in a richtextbox w/o using .rt

hi, im creating an instant messenger using TCP sockets, and everythings been 
fine until now. i want to be able to send rich text, but im hitting a wall 
here. what i want to do is send the font name, style, and color before every 
text so that way it will format it on the other end and display it in another 
richtextbox. right now im trying to send the .rtf property but i cant add it 
to the just wont show anything and wont throw any if someone could help me out here itd b great...heres the code

 Private Sub ReceiveCallBack(ByVal ar As IAsyncResult)

        ''''''      Declarations        ''''''''''''''''''''''''''''''''''
        Static times As Integer = 1
        Static newfile() As File = Nothing

        Static strisfile As String = "text"

        Dim bytes() As Byte = CType(ar.AsyncState, Byte())
        Dim numbytes As Int32 = ClientSocket.EndReceive(ar)

        Dim fs As FileStream
        Dim br As BinaryWriter

        ''''''      End Declarations    ''''''''''''''''''''''''''''''''''

        ''Try block for text creation and file receiving

            ''Catches a null declaration error in array bytes
            If bytes Is Nothing Then

                Exit Sub

            End If

            ''if nothing sent, disconnect
            If numbytes = 0 Then


                Dim dlg As New NoParamsDelegate(AddressOf DisconnectedUI)


                ''else, receive what is sent

                ''get what was sent and convert to string using ASCII encoding
                Dim recv As String = ASCII.GetString(bytes, 0, numbytes)
                Dim tempclr As Color

                ''If recv is "File"
                If recv.Substring(0, 6) = "[File]" Then

                    ''tell to create file
                    strisfile = "[FileName]"

                    Exit Try

                ElseIf recv.Substring(0, 7) = "[/File]" Then

                    ''tell to show whatever is received next as text
                    strisfile = "text"

                    ''flush streams

                    ''close streams

                    Exit Try

                End If

                ''if image variable is 
                If strisfile = "text" Then

                    If times = 1 Then

                        ''sets back color
                        Dim tempstr As String = 
recv.Substring(recv.IndexOf("[") + 1, (recv.LastIndexOf("]") - 
recv.IndexOf("[") - 1))
                        tempclr = Color.FromName(tempstr)

                        times = 2

                    ElseIf times = 2 Then

                        ''sets rtf formatting
                        Me.rtbreceivedtext.Rtf += recv & vbNewLine
                        ''Me.rtbsendtext.SelectedText = recv
                        Me.rtbsendtext.ForeColor = tempclr

                        times = 1

                    End If

                    ''if filevar is "File" then add recv to file
                ElseIf strisfile = "[File]" Then

                    Dim tempbytes As Byte() = ASCII.GetBytes(recv)


                    ''if filevar is "FileName" then create file
                ElseIf strisfile = "[FileName]" Then

                    ''create file
                    fs = New FileStream(Application.StartupPath & "/" & 
recv, FileMode.Create, FileAccess.Write)
                    br = New BinaryWriter(fs)

                    ''file var tell to create file
                    strisfile = "[File]"

                End If

            End If

        Catch ex As Exception

            Call WriteErrors("ReceiveCallBackFirstTryBlock", ex.Message)

        End Try

        ''try block for array clearing

            'Clear the buffer
            Array.Clear(bytes, 0, bytes.Length)

            'Begin Receive Again
            ClientSocket.BeginReceive(bytes, 0, bytes.Length, 
SocketFlags.None, AddressOf ReceiveCallBack, bytes)

        Catch ex As Exception

            Call WriteErrors("ReceiveCallBackSecondTryBlock", ex.Message)

        End Try

    End Sub


6. changing font color in named range using vba

7. Changing the font color using VBA

8. Changing font style/size/color/etc for multiple series in a ch

Return to MS EXCEL


Who is online

Users browsing this forum: No registered users and 6 guest