Conditional Formatting :@

[missjojo]

Hi, I hope someone can help me, this is driving me mad!

I would like to make Cell A turn yellow if there are any blanks in the rest of the row.

[TABLE="width: 204"]
<TBODY>[TR]
[TD="class: xl63, width: 144, bgcolor: #b6dde8"]Name
[/TD]
[TD="class: xl64, width: 128, bgcolor: #b6dde8"]Date
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 144, bgcolor: #b6dde8"]Name (GO YELLOW)
[/TD]
[TD="class: xl64, width: 128, bgcolor: #b6dde8"]Blank
[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 144, bgcolor: #b6dde8"]Name
[/TD]
[TD="class: xl64, width: 128, bgcolor: #b6dde8"]Date
[/TD]
[/TR]
</TBODY>[/TABLE]

I hope this makes sense..

Thank you

 

[Trevor G]

Welcome to the form you could try this

[QUOTE

Excel Workbook
	A	B
1	Check	Against
2	1	0
3	2
4	3
5	4
6	5
7	6	0
8	7	0
9	8	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =ISBLANK($B2)	Abc
B2	1. / Formula is =ISBLANK($A2)	Abc
A3	1. / Formula is =ISBLANK($B2)	Abc
B3	1. / Formula is =ISBLANK($A2)	Abc
A4	1. / Formula is =ISBLANK($B2)	Abc
B4	1. / Formula is =ISBLANK($A2)	Abc
A5	1. / Formula is =ISBLANK($B2)	Abc
B5	1. / Formula is =ISBLANK($A2)	Abc
A6	1. / Formula is =ISBLANK($B2)	Abc
B6	1. / Formula is =ISBLANK($A2)	Abc
A7	1. / Formula is =ISBLANK($B2)	Abc
B7	1. / Formula is =ISBLANK($A2)	Abc
A8	1. / Formula is =ISBLANK($B2)	Abc
B8	1. / Formula is =ISBLANK($A2)	Abc
A9	1. / Formula is =ISBLANK($B2)	Abc
B9	1. / Formula is =ISBLANK($A2)	Abc

][/QUOTE]

 

[missjojo]

Thank you for replying.
I have got rows from E-P that I need to check against, that is the part that is confusing me. B-D are hidden.
Just to make things a little bit more difficult :|

Would I need to put the formula's in seperate?

Thanks again for your help.

Welcome to the form you could try this

[QUOTESheet1

*	A	B
Check	Against
*
*
*
*

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #ffff00, align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #ffff00, align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #ffff00, align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]

</TBODY>

Conditional formatting

Cell Nr.: / Condition Format A2 1. / Formula is =ISBLANK($B2) <TBODY> [TD="bgcolor: #ffff00"]Abc

<TBODY>

B2	1. / Formula is =ISBLANK($A2)
A3	1. / Formula is =ISBLANK($B2)
B3	1. / Formula is =ISBLANK($A2)
A4	1. / Formula is =ISBLANK($B2)
B4	1. / Formula is =ISBLANK($A2)
A5	1. / Formula is =ISBLANK($B2)
B5	1. / Formula is =ISBLANK($A2)
A6	1. / Formula is =ISBLANK($B2)
B6	1. / Formula is =ISBLANK($A2)
A7	1. / Formula is =ISBLANK($B2)
B7	1. / Formula is =ISBLANK($A2)
A8	1. / Formula is =ISBLANK($B2)
B8	1. / Formula is =ISBLANK($A2)
A9	1. / Formula is =ISBLANK($B2)
B9	1. / Formula is =ISBLANK($A2)

[TD="bgcolor: #31869b"]Abc[/TD]

[TD="bgcolor: #ffff00"]Abc[/TD]

[TD="bgcolor: #31869b"]Abc[/TD]

[TD="bgcolor: #ffff00"]Abc[/TD]

[TD="bgcolor: #31869b"]Abc[/TD]

[TD="bgcolor: #ffff00"]Abc[/TD]

[TD="bgcolor: #31869b"]Abc[/TD]

[TD="bgcolor: #ffff00"]Abc[/TD]

[TD="bgcolor: #31869b"]Abc[/TD]

[TD="bgcolor: #ffff00"]Abc[/TD]

[TD="bgcolor: #31869b"]Abc[/TD]

[TD="bgcolor: #ffff00"]Abc[/TD]

[TD="bgcolor: #31869b"]Abc[/TD]

[TD="bgcolor: #ffff00"]Abc[/TD]

[TD="bgcolor: #31869b"]Abc[/TD]

</TBODY>

[/TD]

</TBODY>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4
]

[/QUOTE]

 

[robw]

Beware of ISBLANK... it doesn't always do what you really might want. Sure, it returns TRUE if the source cell has nothing in it (eg. has been cleared using the delete key) but returns FALSE if the source cell contains, say, one or more space characters . It also returns FALSE if the source cell contains a formula, even if that formula resolves to a null value.

 

[missjojo]

Yes your right, it's not working how I would like it too.
It's completely confusing me.
Thanks for your reply, I will keep trying.

 

[robw]

I have used the following formula in the past where ISBLANK has let me down....

=(SUMPRODUCT(--(TRIM(A1:A10)<>""))>0)

It returns FALSE if all cells A1 through A10 are blank, empty, contain only spaces or contain a formula that evaluates to null, empty or space etc
or TRUE otherwise

 

[missjojo]

Thank you for your reply.

I think that has worked, however when I enter text in the row the cell doesn't go back to the original colour. Is there a way to do this too?

TIA

I have used the following formula in the past where ISBLANK has let me down....

=(SUMPRODUCT(--(TRIM(A1:A10)<>""))>0)

It returns FALSE if all cells A1 through A10 are blank, empty, contain only spaces or contain a formula that evaluates to null, empty or space etc
or TRUE otherwise

 

[Rick Rothstein]

I think that has worked, however when I enter text in the row the cell doesn't go back to the original colour. Is there a way to do this too?

After highlighting the cells in Column A (starting with Row 1), I believe the Conditional Formatting formula you will want to use for your particular layout is...

=SUMPRODUCT(--(TRIM(E1:P1)<>""))=0