Results 1 to 9 of 9

Thread: Slightly tricky conditional formatting
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,520
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Slightly tricky conditional formatting

    I have this code I want it to do a job for me but it's not finishing it properly.

    Code:
    Dim eCell As Range
    For Each eCell In [E5:E18]
              eCell.Offset(eCell.Row-5, -1).Font.Color = eCell.Font.Color
               eCell.Offset(eCell.Row-5, -3).Font.Color = eCell.Font.Color
    Next eCell
    The aim is to make the font colors of the row from col B to Col D same as the the font color in E. Meanwhile col C is excluded.

    I am using a conditional formatting for col E.

    I am in for a better way to get this done.

    Thanks
    There Is Always A Better Way

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

    Default Re: Slightly tricky conditional formatting

    If you wants to 'recopy' color of cell which is results of Conditional Formatting is not a way.
    It could works (not tested) for hardcoded color.

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

    Default Re: Slightly tricky conditional formatting

    Okay

    So I decide to avoid the conditional formatting and used a code to set the font colors.

    It's working now just that I am having issues with the offset.

    The font color in col E does not match the B and D.
    There Is Always A Better Way

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

    Default Re: Slightly tricky conditional formatting

    Based on your OP using CF, how about
    Code:
    Dim eCell As Range
    For Each eCell In [E5:E18]
              eCell.Offset(, -3).Font.Color = eCell.DisplayFormat.Font.Color
             eCell.Offset(, -1).Font.Color = eCell.DisplayFormat.Font.Color
    Next eCell
    But wouldn't it be easier to do it conditional formatting?
    Last edited by Fluff; Aug 19th, 2019 at 06:29 AM.
    - 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 kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,520
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Slightly tricky conditional formatting

    @Fluff, it worked great!!!

    As with the conditional formatting I am stacked. you can point me to that direction so I decide which will be sweet for me.
    There Is Always A Better Way

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

    Default Re: Slightly tricky conditional formatting

    What formula are you using for the CF?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Slightly tricky conditional formatting

    I used
    ="$E5<>YES"
    while selecting the various columns
    There Is Always A Better Way

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

    Default Re: Slightly tricky conditional formatting

    If you select B5:B18 and D5:E18 then use
    =$E5="YES"
    that should format all three columns if col E=YES
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Slightly tricky conditional formatting

    Okay I will give it a try
    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
  •