+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting cell to RED and display text

  1. #1
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Hobart, Tasmania, Australia
    MS-Off Ver
    Office 2010/2016
    Posts
    208

    Conditional Formatting cell to RED and display text

    Hi All,

    I have a spread sheet that check if an activity has been selected using
    Please Login or Register  to view this content.
    Is there a way to add text to populate the cell as well as the coloring.
    So something like "Activity already entered"

    Only other problem is, the selection is made from data validation, so users select program > then activity.
    Last edited by russkris; 06-08-2017 at 08:00 PM. Reason: Added Solved tag

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,848

    Re: Conditional Formatting cell to RED and display text

    In the cell in question, this:

    =IF(COUNTIF($A$17:$A17, A17)>1,"Activity already entered","")

    Then it's just a CF rule using the cells that contain option to colour cells containing that text string.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,123

    Re: Conditional Formatting cell to RED and display text

    or just...
    =IF(COUNTIF($A$17:$A17, A17),"Activity already entered","")

    Using DV is no different to entering data manually, all it does is restrict what can be entered, save some typing and help avoid typos, but the actual cell contents is no different.

    CF rule would be just =COUNTIF($A$17:$A17, A17)
    use Use Formula
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Hobart, Tasmania, Australia
    MS-Off Ver
    Office 2010/2016
    Posts
    208

    Re: Conditional Formatting cell to RED and display text

    I probably didn't explain myself well enough, apologies for that.

    Actually I think when I pasted the formula it cut some of my text.

    Screenshot_4.png

    =COUNTIF($A$17:$A17, A17)>1 turns the cell red, if the same thing is entered twice.
    Next to the Program(B) heading is Column A, that has =B17&C17&D18

    Then I have a CF on B17:B42

    Screenshot_2.pngScreenshot_3.png

    If I use the suggestion above, the cells no longer appear Red and the text doesn't show. Am I entering them incorrect?

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Conditional Formatting cell to RED and display text

    You say 'Column A, that has =B17&C17&D18' - I assume that should be '=B17&C17&D17'.

    Apply this CF formula to your B17:D42 range:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note the addition of the last $ so that the formula will look at column A for all three columns.

    Now, to add some warning text as well, when you select red fill from the 'Format' button, don't immediately click 'OK'. Instead, go to the 'Number' tab, then select 'Custom' at the bottom of the left-hand side. Enter this in the box on the right-hand side:
    ;;;@" - Activity already entered" (that's three semi-colons before the @-sign)
    That will show the entry as (for example) 'Airport - Activity already entered' instead of just 'Airport'.

    Edit: if you don't want the original text too, delete the @-sign

    The attached sheet has that working, using your data from above.
    Hope that's of some help.
    Last edited by Aardigspook; 06-08-2017 at 07:04 PM. Reason: Extra info
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  6. #6
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Hobart, Tasmania, Australia
    MS-Off Ver
    Office 2010/2016
    Posts
    208

    Re: Conditional Formatting cell to RED and display text

    Quote Originally Posted by Aardigspook View Post
    You say 'Column A, that has =B17&C17&D18' - I assume that should be '=B17&C17&D17'.
    Ah yes, apologies, typo.

    Quote Originally Posted by Aardigspook View Post
    Apply this CF formula to your B17:D42 range:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note the addition of the last $ so that the formula will look at column A for all three columns.

    Now, to add some warning text as well, when you select red fill from the 'Format' button, don't immediately click 'OK'. Instead, go to the 'Number' tab, then select 'Custom' at the bottom of the left-hand side. Enter this in the box on the right-hand side:
    ;;;@" - Activity already entered" (that's three semi-colons before the @-sign)
    That will show the entry as (for example) 'Airport - Activity already entered' instead of just 'Airport'.

    Edit: if you don't want the original text too, delete the @-sign

    The attached sheet has that working, using your data from above.
    Hope that's of some help.
    That's a lovely little trick. Thanks heaps.

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Conditional Formatting cell to RED and display text

    You're welcome, glad to be of help and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 01-16-2014, 03:55 AM
  2. Replies: 3
    Last Post: 11-04-2013, 12:47 AM
  3. Cell formatting for number and text display
    By breadwinner in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-25-2013, 03:44 AM
  4. [SOLVED] conditional formatting or VBA to display text
    By gocolonel77 in forum Excel General
    Replies: 7
    Last Post: 06-06-2013, 11:09 PM
  5. Replies: 6
    Last Post: 06-10-2012, 03:57 PM
  6. display text and its formatting in other cell
    By Ricky's Dad in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-21-2008, 10:20 PM
  7. [SOLVED] Why won't my conditional formatting display in the cell
    By Cashius War eagle in forum Excel General
    Replies: 4
    Last Post: 02-15-2005, 06:45 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1