+ Reply to Thread
Results 1 to 14 of 14

Conditional Formatting (changing color) in one cell based on text in two cells

  1. #1
    Registered User
    Join Date
    02-12-2013
    Location
    East Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Conditional Formatting (changing color) in one cell based on text in two cells

    Hello:

    I have been trying to use what I thought would be a simple formula to change the color of one cell based on text in that cell and having text in the first cell of the same row. Using conditional formatting, I thought of using a formula in A2 as follows: =AND(ISTEXT(A1),ISTEXT(A2)) but it doesn't seem to work. Also, if and when the formula may work I would want to copy it across the row keeping A1 constant and changing A2 to A3,A4,A5,etc.... Any help would be GREATLY appreciated.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    If you are going across a row, it will be A1, B1, C1, etc, not A1, A2, (that's down a column)
    Conditional Formatting>New Rule> Formula
    =AND(ISTEXT($A1), ISTEXT(B1))
    Format as desired
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-12-2013
    Location
    East Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    Perfect!! Thanks!!! and yes you are correct on rows and columns. I was about to pull my hair out on such a simple thing that I am not seeing straight......

  4. #4
    Registered User
    Join Date
    02-12-2013
    Location
    East Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    Well I may have spoken too soon... It seems to work when both cells have text and it changes the color but then after deleting text in one or the other cell, the color stays in the cell (it does not turn back to white...)

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    How are you deleting the text? If you are doing this by pressing the space bar, you are putting a blank space " " into the cell. Excel sees this as text. Either backspace or "Clear Contents"

  6. #6
    Registered User
    Join Date
    02-12-2013
    Location
    East Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    I am deleting contents by backspace or delete key.. It seems to be a "refresh" issue
    Although F9 doesn't update the color. Navigating off the current page and then back works
    Though!! Strange. Thanks for your help so far.!!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    any chance of seeing a sample workbook of what you are working with?
    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

  8. #8
    Registered User
    Join Date
    02-12-2013
    Location
    HYD
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    i would like excel to generate an popup alert if ANY of my cells from E3:H16 is less than Number 40. Even I don't now how to enter in VBA script mode. I am new to xcel. Please advise. Thanks

  9. #9
    Registered User
    Join Date
    02-12-2013
    Location
    East Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    I think I have attached the file I am trying to use. My basic idea is only to have the cells shaded when someone is working the shift so the manager can see at a glance what coverage they have.
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    There are two ways to fix your problem:

    1. Copy the sheet to a new sheet. The new sheet will work as you expect.
    2. If you can open the VBA window and more importantly, the Properties window, there is a property for your original sheet called "EnableFormatConditionsCalculation" that is set to False. Change it to True and this will also fix your problem.

  11. #11
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    There are two ways to fix your problem:

    1. Copy the sheet to a new sheet. The new sheet will work as you expect.
    2. If you can open the VBA window and more importantly, the Properties window, there is a property for your original sheet called "EnableFormatConditionsCalculation" that is set to False. Change it to True and this will also fix your problem.

  12. #12
    Registered User
    Join Date
    02-12-2013
    Location
    East Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    Thanks so much!!! The VBA tab worked and now the cells update instantly as I wanted.

    Thanks again

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  14. #14
    Registered User
    Join Date
    02-14-2013
    Location
    bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Conditional Formatting (changing color) in one cell based on text in two cells

    Thank you verymuch friends....its soo helpful for me.
    thanks alot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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