Conditional Formatting Not Working Consistently for All Affected Cells

[Judoman]

Problem: Conditional formatting not working consistently for all cells defined.


Scenario: Need to cover a row with hash marks to blank it out when wrong part number is listed.

Row A37:F37 is covered by blue hashes if X is in A7 and C12 = TT60000678-
Part number in C12 is entered by formula when X is put in A7
To change part number in C12 requires an X in D22 or E22. Formula for this is in C12 .
=IF(AND(OR(D22="X",E22="X"),A7="X"), "TT60003571-",IF(AND(A7="X",(E4<=292)),"TT60000678-",IF(AND(A7="X",E4>=293,E4<=600),"TT60002639-",
IF(AND(A7="X",E4>=701,E4<=730),"TT60000678-",IF(AND(A7="X",E4>=601,E4<=604),"TT60000678-",IF(AND(A7="X",E4>=731),"TT60002639-", ""))))))


Conditional Formatting Formula for adding hash marks in A37:F37 works fine when X is put in E22.
It does not work when X is put in D22

 

[Judoman]

I'm still working on solving this issue. I can make conditional formatting work for 1 cell but not for 2 cells side by side. I think it should work by using an AND(OR formula. But it does not work.
Here's latest formula I'm trying: =AND(OR($D22<>"X",$E22<>"X"),$A7="X").

Result I get is cells get hash marked like I want when I enter X in A7. But adding another X in D22 or E22 doesn't remove hash marks. Only way to remove hash marks is with X in both D22 and E22.

Do I have the formula written correctly for and(or in conditional formatting?

Is this even possible?

 

[Judoman]

What I have now is that putting an X in a certain cell covers a row with hash marks. Putting X in another cell does nothing. Both cells are identified correctly in formula. I tried this on a blank excel sheet and it worked. Just can't get it to work on the sheet I need it to work on.

Here's the formula:
=(AND($A7="X",$E$22<>"X",$D$22<>"X"))

X in A7 & E22 causes hash marks to appear. Does not work when I put X in D22. Tried it with a formula for cells in different columns. Only works on cells in Col. E

 

[Eric W]

Your use of $ signs seems a bit inconsistent. Let me see if I can succinctly describe your needs:

If A7 = X, AND (E22 OR D22 is X) then, cover A37:F37 with hash marks. Is that it? You don't have multiple rows on your sheet with multiple part numbers? If so, try:

Select A37:F37. Click Conditional Formatting > New Rule > Use a formula > and enter:
=AND($A$7="X",OR($D$22="X",$E$22="X"))
then select your format.

Let me know if this is what you're looking for.

 

[Judoman]

Eric, thanks for your reply. I tried your formula and several variations. I can get them all to work with an X in E22, but not with X in D22. Tried using several other cells/columns for X reference and they don't work either.

I can get formulas to work correctly in a new blank excel worksheet. Must be something hidden in my real form.

Here's current formula I have in new worksheet that works fine:
=AND($A3="X",AND($B$8<>"X",$C$8<>"X"))

Here's same formula in my actual form and it does not work correctly - X in D22 does nothing.
=AND($A7="X",AND($D$22<>"X",$E$22<>"X"))

Any suggestions?

 

[FDibbins]

=AND($A7="X",AND($D$22<>"X",$E$22<>"X"))

That worked for me. Below, A7 contains X

	A​	B​	C​	D​
22​	TRUE​

no x's in row 22 above - formula shows TRUE

1 x below, formula shows FALSE

	A​	B​	C​	D​	E​
22​	FALSE​			x

Another X in row 22 - still shows FALSE

	A​	B​	C​	D​	E​
22​	FALSE​				x

2 x's in row 22 - still shows FALSE...

	A​	B​	C​	D​	E​
22​	FALSE​			x	x

You also use this formula...
=AND(COUNTIF(D22:E22,"x")=0,A7="x")

 

[Judoman]

Ford,

So why won't the good formula work in my real order form?

Could not get your countif formula to work. Best it did was hash marks in 1 of 4 cells I'm trying to cover with hash marks. Even then it worked some times but not always in my real order form. It did work on the 1 cell consistently in a blank new worksheet.

 

[FDibbins]

OK how are you applying hash marks to cells? CF normally just changes a cell's formatting (color, bold, font etc)

What is the Applies To range that you are using, does it cover those cells you say are not working?

 

[Judoman]

Hash marks are done in CF by selecting a color and then a pattern. Cells will accept hashmarking OK. Here's how things are supposed to work: Putting X in A7 adds hash marks. This works. Then an X in either D22 or E22 should remove hash marks. This Only works for E22. X in D22 never works - no matter what formula I try.

So the problem is why won't the formula work for D22 but it works OK for E22.