Conditional Formating (?) question

[mmills421]

Dumb user here. I want to set a conditional formatting (?) rule so that a
row will highlight a certain color depending on if certain cells contain a
character (X). Example, if A1, B1, C1 and D1 each contain "X", the row will
highlight GREEN. If "X" is not contained within each of the cells, the row
will highlight RED.

 

[akphidelt]

Alright, highlight the complete row around all your data...

Go to Format-->Conditional Formatting

Change it to Formula Is

For example if the first row of your data starts on A1 to D1

Type in
=And($A1="X",$B1="X",$C1="X",$D1="X")

Make sure you keep the $ sign in front of the column
Then for the no X do the same thing but replace the = sign with <>

=And($A1<>"X",$B1<>"X",$C1<>"X",$D1<>"X")

 

[mmills421]

Thanks, it worked for the 1st condition (GREEN) but not for the second
condition (RED). I double checked the formula and it looks correct.

Also, how would I get this to apply to all rows, without having to re-type
conditions for each, i.e. A1, B1, C1 and then A2, B2, C2.

 

[mmills421]

Actually, the RED formula works, but only if the none of the cells contains
an "X". I would like it to highlight RED if and single cell does not contain
an X, so that the row would be highlighted RED until ALL cells contain an
"X", at which point the row will switch to GREEN.

 

[akphidelt]

If you highlight all the rows of your data and you keep the $ sign in front
of the column letter, you should only have to type the equation in once.

And remember if your data does not start in A1 then change the 1 to whatever
row your data starts in.

As for way the second formula is not working I am not sure. Can you post the
formula here... and then let me know where the conditional formatting starts?

 

[akphidelt]

Ohhhh, ok then change the formula from

=And($A1<>"X",$B1<>"X",$C1<>"X",$D1<>"X") To

=Or($A1<>"X",$B1<>"X",$C1<>"X",$D1<>"X")

Basically just change And to Or

 

[David Biddulph]

Change AND to OR in the RED formatting formula, or merely make RED the
default format, which would apply unless the GREEN condition were met.

 

[mmills421]

Ok, working now. One last question though, now all cells are red unless the
GREEN conditions are met. I want all rows that don't have column A values to
appear clear. Once a value is put into column A, I want the GREEN/RED rules
to apply. GREEN and RED are for the "X" value in columns B-F, but nothing
should apply if column A is blank. I appreciate all your help by the way...

 

[mmills421]

OK, thanks for the help. One last question though. All rows that don't have
required "X" value appear RED, but I would like the RED/GREEN rules to only
apply after a value is entered into column A. The "X" columns are B-F, so I
would like the RED/GREEN rules to be "activated" if something is written in
column A. Until a value is in column A, the rows should appear clear. I
appreciate all of your help...

 

[akphidelt]

No problem! Try changing the second condition too

=And($A1="X",Or($B1<>"X",$C1<>"X",$D1<>"X"))

 

[mmills421]

That is almost working, but when I enter a value in column A of a row, the
row underneath becomes active (highlighted RED), not the actual row in which
the value was entered...