Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula returns color to "yes" and"no"

    Is there a formula that returns color (to the cell or the words) when the answers are "yes" and "no".... (not a conditional format -- a real formula...

    Thank you in advance..

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    This is cross-posted at http://www.eileenslounge.com/viewtop...49a9d0796a749a. That forum has answers in it, so please go there for any followup and additional responses.

    Please examine the rules about cross-posting at http://windowssecrets.com/forums/faq...n#crossposting and also read the message to cross-posters at http://www.excelguru.ca/content.php?184

    Steve

  4. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    Tech Teacher,

    You can do this easily with vba. Just type yes/no/or anything in a cell. Upper/lower case makes no difference.

    Place either code in the worksheet module.

    HTH,
    Maud


    code to change cell interior color
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If UCase(Target) = "YES" Then
            Target.Interior.ColorIndex = 4
        ElseIf UCase(Target) = "NO" Then
            Target.Interior.ColorIndex = 3
        Else:
            Target.Interior.ColorIndex = 0
        End If
    End Sub
    Code to change font color
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If UCase(Target) = "YES" Then
            Target.Font.ColorIndex = 4
        ElseIf UCase(Target) = "NO" Then
            Target.Font.ColorIndex = 3
        Else:
            Target.Font.ColorIndex = 0
        End If
    End Sub

  5. #4
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    225
    Thanks
    30
    Thanked 4 Times in 4 Posts
    Which also shows you which forum is best. Eileens Lounge said it couldn't be done - this one provides the answer as to how it CAN be done.

    I know where I'm staying

    Alan

  6. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    852
    Thanks
    13
    Thanked 56 Times in 56 Posts
    Quote Originally Posted by sdckapr View Post
    This is cross-posted at http://www.eileenslounge.com/viewtop...49a9d0796a749a. That forum has answers in it, so please go there for any followup and additional responses.

    Please examine the rules about cross-posting at http://windowssecrets.com/forums/faq...n#crossposting and also read the message to cross-posters at http://www.excelguru.ca/content.php?184

    Steve
    In all fairness to the OP he only posted here after he was told "I'm sorry, formulas cannot set formatting."

  7. #6
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,199
    Thanks
    45
    Thanked 228 Times in 211 Posts
    Alan,

    I agree, all the members in this forum are great; those who present the challenge and those who help resolve them. But in all fairness to Eileen's lounge, Tech Teacher asked for a formula not a macro. Those that responded in the other forum are absolutely correct that it was not possible with a formula. So was solution to his enquiry really answered or was he merely provided a workaround?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •