Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: VBA code to change font color from red to black
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2015
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code to change font color from red to black

    Hi all,

    I´m looking for a code that changes in all the workbook, all the text that is in red colored (red font), into black (black font).

    Would it be possible?

    Thanks in advance!

  2. #2
    Board Regular
    Join Date
    Apr 2019
    Posts
    80
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to change font color from red to black

    1 most important question: red color is hard coded or results of CF?
    if hardcoded no problem: loop through all sheet, cells on each, check color and change.

  3. #3
    Board Regular
    Join Date
    May 2015
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to change font color from red to black

    Hello.

    It is hardcoded. Is there a code for this?

    Thanks!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA code to change font color from red to black

    Do you have any cells with a font colour other than red, or black that needs to remain as-is?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    May 2015
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to change font color from red to black

    Hello,

    Yes, I have fonts in white, red, black and another colours.

    It would be perfect to have a code that finds every text in red in the workbook and change the color into black.

    Thanks!

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

    Default Re: VBA code to change font color from red to black

    Quote Originally Posted by gubertu View Post
    Hello,

    Yes, I have fonts in white, red, black and another colours.

    It would be perfect to have a code that finds every text in red in the workbook and change the color into black.

    Thanks!

    This one is going to be tricky. You are going to have to do something for me if you want this to work.

    Run this code against your sheet. You will see I have Range("A1")...Please edit that to a Range that is currently the shade of red you are using.




    Code:
    Sub Get_Color_Code()
        Range("A1").Select
        MsgBox Selection.Font.Color
    End Sub
    Please post the color code. I suspect it will likely be 255.
    Last edited by Steve_; Jul 10th, 2019 at 09:16 AM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA code to change font color from red to black

    Ok, how about
    Code:
    Sub gubertu()
       Dim Ws As Worksheet
       
       With Application
          .FindFormat.Clear
          .FindFormat.Font.Color = 255
          .ReplaceFormat.Clear
          .ReplaceFormat.Font.ColorIndex = xlAutomatic
       End With
       
       For Each Ws In Worksheets
          Ws.UsedRange.Replace "", "", xlPart, , , , True, True
       Next Ws
    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

  8. #8
    Board Regular
    Join Date
    May 2015
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to change font color from red to black

    Hello, this is working almost perfect! But... what if in the same text there are part text in black font and part text in red font? In this case, it is not changed to black.

    Thanks again!

    Example: ASSETS in (-)
    Last edited by gubertu; Jul 10th, 2019 at 10:35 AM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA code to change font color from red to black

    No, it won't be. The only way I know to do that would be to loop through every single used cell in each sheet, which could take a long time.

    One thing, I forgot to reset the format's in the last code, it should be
    Code:
    Sub gubertu()
       Dim Ws As Worksheet
       
       With Application
          .FindFormat.Clear
          .FindFormat.Font.Color = 255
          .ReplaceFormat.Clear
          .ReplaceFormat.Font.ColorIndex = xlAutomatic
       End With
       
       For Each Ws In Worksheets
          Ws.UsedRange.Replace "", "", xlPart, , , , True, True
       Next Ws
       With Application
          .FindFormat.Clear
          .ReplaceFormat.Clear
       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

  10. #10
    Board Regular
    Join Date
    May 2015
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to change font color from red to black

    Thanks for the last code.

    I have an idea. What if we replace in all the workbook the text "in (-)" that is in red for the text "in (-)" in blackż?

    This will solve my problem.

    Thanks!

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
  •