VBA: Change text colour to red
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA: Change text colour to red

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Change text colour to red

    Hi,
    I need a function not macro.

    to change Column I , ERROR! to red.

    e.g

    ERROR! Pls check the file
    ERROR! Pls check the folder

    I know can use conditional formulas but conditional dont found at older excel format.
    SO i like to use vba
    Last edited by harky; Jul 21st, 2019 at 10:13 AM.

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Change text colour to red

    Code:
    Public Sub changeColor() 
    
    Set wks = Worksheets("sheet1")
    wks.Range("I" & i)
    .Characters.Text = "ERROR!"
    Font.ColorIndex = 3 
    
    End Sub
    Last edited by harky; Jul 21st, 2019 at 10:30 AM.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,729
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA: Change text colour to red

    How about
    Code:
    Sub harky()
       Dim Cl As Range
       Dim x As Long
       With Sheets("Sheet1")
          For Each Cl In .Range("I1", .Range("I" & Rows.Count).End(xlUp))
             x = InStr(1, Cl, "ERROR", vbTextCompare)
             If x > 0 Then Cl.Characters(x, 5).Font.Color = vbRed
          Next Cl
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Change text colour to red

    This work great but possible not as MACRO?

    As long i open the excel it auto highlighted it?

    Quote Originally Posted by Fluff View Post
    How about
    Code:
    Sub harky()
       Dim Cl As Range
       Dim x As Long
       With Sheets("Sheet1")
          For Each Cl In .Range("I1", .Range("I" & Rows.Count).End(xlUp))
             x = InStr(1, Cl, "ERROR", vbTextCompare)
             If x > 0 Then Cl.Characters(x, 5).Font.Color = vbRed
          Next Cl
       End With
    End Sub

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,729
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA: Change text colour to red

    Sorry but I don't understand what you mean.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Change text colour to red

    Automatically run a macro when opening a excel

    I try to set Private Sub but it dont works

    Quote Originally Posted by Fluff View Post
    Sorry but I don't understand what you mean.
    Last edited by harky; Jul 21st, 2019 at 10:58 AM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,729
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA: Change text colour to red

    In that case use
    Code:
    Private Sub Workbook_Open()
       Dim Cl As Range
       Dim x As Long
       With Sheets("Sheet1")
          For Each Cl In .Range("I1", .Range("I" & Rows.Count).End(xlUp))
             x = InStr(1, Cl, "ERROR", vbTextCompare)
             If x > 0 Then Cl.Characters(x, 5).Font.Color = vbRed
          Next Cl
       End With
    End Sub
    and put it in the ThisWorkbook module
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Change text colour to red

    ah.. cool. this work great..
    Thank u!!!!!!!

    Quote Originally Posted by Fluff View Post
    In that case use
    Code:
    Private Sub Workbook_Open()
       Dim Cl As Range
       Dim x As Long
       With Sheets("Sheet1")
          For Each Cl In .Range("I1", .Range("I" & Rows.Count).End(xlUp))
             x = InStr(1, Cl, "ERROR", vbTextCompare)
             If x > 0 Then Cl.Characters(x, 5).Font.Color = vbRed
          Next Cl
       End With
    End Sub
    and put it in the ThisWorkbook module

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,729
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA: Change text colour to red

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Change text colour to red

    Hi, i just notice the code,

    If my column was highlighted in black at the beginning. it will show this BUT
    I
    Status
    ERROR Pls check the file



    If i re-activate my exiting MACRO and re-fill the column again. It will highlight the whole text in red.
    I
    Status
    ERROR Pls check the file

    Quote Originally Posted by Fluff View Post
    You're welcome & thanks for the feedback

    Last edited by harky; Jul 21st, 2019 at 08:34 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •