Results 1 to 9 of 9

Thread: VBA code to set font color to match background color
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,510
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code to set font color to match background color

    Hello

    I need a script that will set font color to match that of the cell background color.

    My aim is to hide the texts in the cells. I have groups of rows with different background colors.

    The data range is A20:O68


    Thanks in advance
    There Is Always A Better Way

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

    Default Re: VBA code to set font color to match background color

    How about
    Code:
    Sub kellymort()
       Dim Cl As Range
       For Each Cl In Range("A20:O68")
          Cl.Font.Color = Cl.Interior.Color
       Next Cl
    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

  3. #3
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,510
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to set font color to match background color

    Great!!!

    I appreciate that.

    Have a nice time
    There Is Always A Better Way

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

    Default Re: VBA code to set font color to match background color

    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

  5. #5
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,350
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA code to set font color to match background color

    Alternatively, if you just want to hide the data in the cells, you could change the number format as follows:
    Code:
    Range("A20:O68").NumberFormat = ";;;"
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,350
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA code to set font color to match background color

    You can wrap that in a handy Property as follows :
    Code:
    Property Let HideData(ByVal Rng As Range, ByVal Hide As Boolean)
        Rng.NumberFormat = IIf(Hide, ";;;", "General")
    End Property
    Then you just hide\show the data as foolows :
    Code:
    Sub HideTheData()
       HideData(Range("A20:O68")) = True
    End Sub
    
    
    Sub ShowTheData()
       HideData(Range("A20:O68")) = False
    End Sub
    Last edited by Jaafar Tribak; Jul 22nd, 2019 at 08:48 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  7. #7
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,510
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to set font color to match background color

    Cool @Jaafar Tribak

    How do I access the property from other modules as well?
    Last edited by kelly mort; Jul 22nd, 2019 at 09:05 AM.
    There Is Always A Better Way

  8. #8
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,350
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA code to set font color to match background color

    Quote Originally Posted by kelly mort View Post
    Cool @Jaafar Tribak

    How do I access the property from other modules as well?
    I just saw this:

    The Property is Public so it should be accessible throughout the whole project as it is.

    You should also fully qualify the range whose data you want to hide from view.

    example :

    HideData(Sheet1.Range("A20:O68")) = True
    Last edited by Jaafar Tribak; Jul 22nd, 2019 at 11:00 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  9. #9
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,510
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to set font color to match background color

    Okay it just worked
    There Is Always A Better Way

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
  •