Results 1 to 9 of 9

Thread: Conditional format based on various cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2011
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional format based on various cells

    Could anyone please advise on conditional formatting?

    I'm trying to use a formula to have say cell a1 to change colour IF cell d1 has any input?

    I've been trying conditional formatting in cell a1 as - If(d1="",TRUE,FALSE) but no change happens with input to cell d1.

    Any help would be greatly appreciated.
    Last edited by smcelv; Sep 19th, 2019 at 06:41 AM.

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

    Default Re: Conditional format based on various cells

    How about
    =D1<>""
    - 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
    Join Date
    Jul 2011
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional format based on various cells

    I tried '=IF(G14<>"",TRUE,FALSE)' but doesn't seem to work.
    Last edited by smcelv; Sep 19th, 2019 at 07:59 AM.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,384
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Conditional format based on various cells

    Quote Originally Posted by smcelv View Post
    I tried '=IF(G14<>"",TRUE,FALSE)' but doesn't seem to work.
    What does column G have to do with anything? It wasn't mentioned in post 1.

    Fluff's suggestion works for me as I understand what you wrote in post 1.

    CF

    ABCD
    1 3
    2 ddd
    3
    4 text
    5 23.2
    6
    7 x

    Conditional formatting
    CellNr.: / ConditionFormat
    A11. / Formula is =D1<>""Abc


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Jul 2011
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional format based on various cells

    Apologies Peter I was using A1 and D1 just as an example. My worksheet has a number of cells that I would need to meet the condition.

    I was just hoping that I could grasp the idea from a example supplied here


    Thank you so much to you and fluff for the support.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Conditional format based on various cells

    In that case, maybe something like
    =COUNTA(D1,G4,C15,E5)=4
    - 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
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,384
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Conditional format based on various cells

    Quote Originally Posted by smcelv View Post
    I was just hoping that I could grasp the idea from a example supplied here
    So, have you been able to do that?

    BTW, in Conditional Formatting you never need something like
    =IF(condition, TRUE,FALSE)

    Just state the condition
    =condition
    and it will be itself TRUE or FALSE and the CF will work on that.
    That is what Fluff's & my formulas do - no IF()
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular
    Join Date
    Jul 2011
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional format based on various cells

    Good morning fluff and Peter.
    Fluff I have used the COUNTA function and have been getting the desired result
    I couldn't get the D1,G4,C15,E5=4 to work for me properly.
    I've had to go to each individual cell and use CF with 'COUNTA(G4). Next cell COUNTA(G5) and so on.

    It certainly does what I require but I'm sure you guys will scoff at that.
    I tried using a range ie. COUNTA(G4:G20) but that didn't work.
    Could you just explain what =4 at the end of your formula meant please fluff?
    Last edited by smcelv; Sep 20th, 2019 at 04:53 AM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Conditional format based on various cells

    Counta counts any cell that is not completely empty, as I used 4 cells in the example I used =4, so if all 4 cells contain something counta will return 4 so you end up with 4=4 which returns true.

    If you had 5 cells in the counta formula you would need to change it to =5
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •