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

1. ## 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. ## 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?

3. ## 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. ## 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. ## 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. ## 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. ## 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?

8. ## 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. ## 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.

10. ## 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. ## 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. ## 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

14. ## 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.

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

#### 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