none
Dynamically change the color of a text of HTML file using vba RRS feed

  • Question

  • Hi Everyone,

    I am creating a HTML file by using VBA. The content of the HTML is getting created from an excel sheet. In one of the column of my excel sheet contains the value like XX & YY and these values will be populated in a web table of HTML files.

    I want to change the color of XX & YY text. When the file will be generated the vba code will search for all XX & YY in the HTML source code and then change their color to Red & Blue respectively. For example:

    If xx then Red and if YY then Blue.

    I'm using the following code. Can anyone please let me know how to modify this code in order to achieve the desired result.

    Dim myFileName As String
        Dim myHTML As String
        Dim FileNum As Integer
        Dim myC As Integer
        Dim myR As Long
        Dim retVal As Variant
         Dim b As Long
        
    
        myFileName = ThisWorkbook.Path & "\Test.html"
        
        With Worksheets("Sheet2")
            b = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
            .Range("H8").Formula = "=COUNTIF(C:C,""XX"")"
            .Range("H9").Formula = "=COUNTIF(C:C,""YY"")"
           
            myHTML = "<HTML>"
            myHTML = myHTML & Chr(10) & "<HEAD>"
            myHTML = myHTML & Chr(10) & "<META HTTP-EQUIV=""Content-Type"" CONTENT=""text/html; charset=windows-1252"">"
            myHTML = myHTML & Chr(10) & "<META NAME=""Generator"" CONTENT=""Microsoft Word 97"">"
            myHTML = myHTML & Chr(10) & "</HEAD>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"" style=""float: left"">"
            myHTML = myHTML & Chr(10) & "<TR>"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""4"">"
            myHTML = myHTML & Chr(10) & "<H3><BR>Result</H3></TH>"
            myHTML = myHTML & Chr(10) & "</TR>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 1).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 2).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 3).Value & "</TH>"
            myHTML = myHTML & Chr(10) & "<TH>" & .Cells(2, 4).Value & "</TH>"
            
            For myR = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row
                
                myHTML = myHTML & "<TR><TD>" & .Cells(myR, 1).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 2).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 3).Value & "</TD>" & _
                                        "<TD>" & .Cells(myR, 4).Value & "</TD></TR>"
            Next myR
            myHTML = myHTML & Chr(10) & "</TABLE>"
            myHTML = myHTML & Chr(10) & "<TABLE BORDER=""5"" style=""float: left"">"
            myHTML = myHTML & Chr(10) & "<TH COLSPAN=""2""><H3><BR>Status</H3></TH>"
            myHTML = myHTML & "<TR><TD>" & "Total Point" & "</TD><TD>" & b & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total XX" & "</TD><TD>" & .Cells(8, 8).Value & "</TD></TR>"
            myHTML = myHTML & "<TR><TD>" & "Total YY" & "</TD><TD>" & .Cells(9, 8).Value & "</TD></TR>"
            myHTML = myHTML & Chr(10) & "</TABLE><br>"
            myHTML = myHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY>"
            myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
             myHTML = myHTML & Chr(10) & "</HTML>"
            myHTML = Replace(myHTML, Chr(10), "</p><p>")
            On Error GoTo ErrHandler
            FileNum = FreeFile
            Open myFileName For Output As #FileNum
            Print #FileNum, myHTML
            Close #FileNum
        End With

    Thanks.


    • Edited by Ed_Dao Thursday, July 9, 2015 5:40 PM
    Thursday, July 9, 2015 5:38 PM

Answers

  • Try...

    "<TD>" & IIf(.Cells(myR, 2).Value = "XX", "<span style=""color: red;"">" & .Cells(myR, 2).Value & "</span>", _
        IIf(.Cells(myR, 2).Value = "YY", "<span style=""color: blue;"">" & .Cells(myR, 2).Value & "</span>", .Cells(myR, 2).Value)) & "</TD>" & _

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Ed_Dao Sunday, July 12, 2015 6:40 AM
    Friday, July 10, 2015 5:10 PM

All replies

  • Hi Ed,

    You can use the Html font command : <font color="red">This red text!</font>

    So, to take your Data rows, change to:

     "<TD><font color="red">" & .Cells(myR, 2).Value & "</font></TD>" & _


    Brian, ProcessIT- Hawke&#39;s Bay, New Zealand

    Thursday, July 9, 2015 9:16 PM
  • Hi Brian,

    Thanks for your reply. Whatever you mentioned is only for changing the color of the text irrespective of its content. But I want if the cell contains XX then XX should be displayed in Red. If it contains YY then YY should be displayed in Blue. 

    Thanks.

    Friday, July 10, 2015 8:46 AM
  • Try...

    "<TD>" & IIf(.Cells(myR, 2).Value = "XX", "<span style=""color: red;"">" & .Cells(myR, 2).Value & "</span>", _
        IIf(.Cells(myR, 2).Value = "YY", "<span style=""color: blue;"">" & .Cells(myR, 2).Value & "</span>", .Cells(myR, 2).Value)) & "</TD>" & _

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Ed_Dao Sunday, July 12, 2015 6:40 AM
    Friday, July 10, 2015 5:10 PM