Conditional formating

[lazarandreiflorin]

Hello,

Is it possible to fill a row with different colors based on any cell from that row. For example I have row F3:R3 and if I type an "x" anywere in this row, I need the whole row to be red but after that if I type a number in another cell I want the row remain red. If I have numbers in all the cells in the row but in one of them is an X it should stay red. When I delete the X or replace it with a number I want the row to become green. If I delete everything in that row I want to have no fill at all. Hope you guys understand what I wrote. Sorry, english is my second language. Thank you

 

[lazarandreiflorin]

Also I don`t know if this matters but in some of the cells are formulas.

 

[etaf]

F3 to R3 for any X stay RED
then you can use a countif
=countif( $f$3:$r$3, "X")>0
that will be true for any X in that range and so we can use in conditional formatting for RED
=SUMPRODUCT(--(ISNUMBER($F$3:$R$3)))=13
Will count to see if all the row has a number in each cell

formulas if they return a zero or number will be counted as a number, otherwise should be ok

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[lazarandreiflorin]

I tested and I see it works but it`s not exactly what I was thinking. Maybe I did not explain it very good. If I have an X in any of the cells it needs to be red (the row). If I have X`s in all the cells then the whole row is red. If I have X`s and numbers in any of the cells (could be just a few cells not all) the row should still be red. If I replace the X`s with numbers and I will have let`s say 3 cells with numbers and the others empty then the row should be green. If delete every number and X it should be no fill at all.

Thank you very much for your help though.

 

[lazarandreiflorin]

F3 to R3 for any X stay RED
then you can use a countif
=countif( $f$3:$r$3, "X")>0
that will be true for any X in that range and so we can use in conditional formatting for RED
=SUMPRODUCT(--(ISNUMBER($F$3:$R$3)))=13
Will count to see if all the row has a number in each cell

formulas if they return a zero or number will be counted as a number, otherwise should be ok

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I mean the one for X ( row to be red ) works just fine.
The other rule for the row to become green only works if I have numbers in all the cells.

 

[mikerickson]

You can do this with two Conditional Formatting conditions.
Select cell F3, and set normal formatting to no fill. The enter these conditions

1) =ISNUMBER(MATCH("X", $F3:$R3, 0)) makes it Red, stop if True
2) =(COUNT($F3:$R3)>0), makes it green, stop if True


Then copy that formatting to the other cells you want formatted that way. Note the relative row / absolute column referencing.

 

[lazarandreiflorin]

You can do this with two Conditional Formatting conditions.
Select cell F3, and set normal formatting to no fill. The enter these conditions

1) =ISNUMBER(MATCH("X", $F3:$R3, 0)) makes it Red, stop if True
2) =(COUNT($F3:$R3)>0), makes it green, stop if True


Then copy that formatting to the other cells you want formatted that way. Note the relative row / absolute column referencing.

Yes this works but still having trouble with the formulas I already have in some of the cells. When I do not have X`s or numbers on that row it still remains green because of the formulas. Here is the attached file.

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[etaf]

=(COUNT($F$3:$W$3)>1)
counts cells that are not blank and formulas counted

what are you trying to count here , thats greater than 1