Conditional Formatting question.
Results 1 to 7 of 7

Thread: Conditional Formatting question.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Formatting question.

    Hi,

    formula question for Excel 2010

    I would like to have 4 cells change colour, if text taken from a range of cells appears within those 4 cells.

    cells I want to change colour G4 to J4

    cells that contain different text options B54 to B75

    Example.

    If I write the text from Cell B60 into cell H4 then cells G4 to J4 turn green.

    thanks,

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,406
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Conditional Formatting question.

    Welcome to the Board!

    If I write the text from Cell B60 into cell H4 then cells G4 to J4 turn green.
    So, are you saying that if the contents of H4 match the contents of B60, then G4:J4 should turn green?
    Then, set up CF in the following way:
    1. Select the cells G4:J4
    2. Go to Conditional Formatting and choose the Formula option (last one)
    3. Enter the following formula:
    =$H$4=$B$60
    4. Choose your green formatting option
    5. Click OK
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting question.

    Quote Originally Posted by Joe4 View Post
    Welcome to the Board!


    So, are you saying that if the contents of H4 match the contents of B60, then G4:J4 should turn green?
    Then, set up CF in the following way:
    1. Select the cells G4:J4
    2. Go to Conditional Formatting and choose the Formula option (last one)
    3. Enter the following formula:
    =$H$4=$B$60
    4. Choose your green formatting option
    5. Click OK
    That scenario does work however i need to formula to work for multiple cases. so...

    if G4 = contents from any cell between B54:B75 turn G4:J4 green
    if H4 = contents from any cell between B54:B75 turn G4:J4 green
    if I4 = etc....
    and if J4 = etc...

    to add more context.

    G4:J4 represent 4 quarters to a month.
    B54:75 represents initals for employees..
    a check needs to be completed per month...

    so the employee would initial in which quarter they have done the check...if the check is done then g4:j4 can be greened out to indicate the check has been completed for that month.

    hope that makes more sense.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,406
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Conditional Formatting question.

    Try something like this for your CF rule:
    Code:
    =((COUNTIF($B$54:$B$75,$G$4) + COUNTIF($B$54:$B$75,$H$4) + COUNTIF($B$54:$B$75,$I$4) + COUNTIF($B$54:$B$75,$J$4)) > 0
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,686
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Conditional Formatting question.

    Try this:

    =SUM((--ISNUMBER(SEARCH(IF($G4:$J4<>"",$G4:$J4),$B$54:$B$75))))

    In Applies to: you can put:

    =$G$4:$J$4

    Or a longer range:

    =$G$4:$J$100
    Regards Dante Amor

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting question.

    Quote Originally Posted by Joe4 View Post
    Try something like this for your CF rule:
    Code:
    =((COUNTIF($B$54:$B$75,$G$4) + COUNTIF($B$54:$B$75,$H$4) + COUNTIF($B$54:$B$75,$I$4) + COUNTIF($B$54:$B$75,$J$4)) > 0

    worked perfectly...thanks a lot

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,406
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Conditional Formatting question.

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

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
  •