Excel - Conditional formatting (by Formula) - few questions

Hello!

I am currently working on some documents where I want to apply background color depending by a variety of conditions.

One example:
fill a whole row from e.g. A1:G100 where the cells C to G are empty
I managed to find a formula which gives me the right boolean result (T/F):
=AND(ISBLANK(INDIRECT("C"&ROW())),ISBLANK(INDIRECT("D"&ROW())),ISBLANK(INDIRECT("E"&ROW())), ... etc. ... )
Placing this formula somewhere outside my data range works perfect, but it just does not work with conditional formatting.

Am I doing something wrong? Maybe the settings?

regards
Pete

Hi Pete,

Am Thu, 3 May 2012 12:15:23 -0700 (PDT) schrieb Pete:


why not trying:
=COUNTBLANK(C1:G1)=5


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks, but I want to keep the formula variable and valid for any row.

So I modified your formula a little:

=COUNTBLANK(INDIRECT("B"&ROW()):INDIRECT("D"&ROW()))=3

but Excel tells me this:

and I even tried this one:

=EXACT(TEXT(COUNTBLANK(INDIRECT("B"&ROW()):INDIRECT("D"&ROW())),0),"3")

but I get the same message.

Well, I know that Excel do not work the same way as any type of programming/scripting language, but the logics should work.

Hi Pete,

Am Thu, 3 May 2012 12:36:17 -0700 (PDT) schrieb Pete:


in CF the formula will adapt for each row.
Select all cells from B1:D & end and use the formula:
=COUNTBLANK(B1:D1)=3


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

WOW. Thank you! I did not even think this could work.
I always have in mind how program languages work, that means "variables".

That would have been my second question:
How do I tell CF which cell to start with or how does CF actually look through all cells specified?

Example:
A Table looks like this and I put CF on A1:E3

1 x a b c x
2 x d e f z
3 y g h i y
...

Now I want every row filled e.g. red, where column A and column E are different.
In this case row 2 should be marked red (-> A2 <> E2, x <> z)
Does Excel/CF have something like a "pointer" or "counter" like for example the popular "i" in loops e.g.: i=1, i=2, i=3, ... or in this case:

I just do not really understand, how formulas are evaluated in CF.

Hi Pete,

Am Thu, 3 May 2012 13:06:47 -0700 (PDT) schrieb Pete:


select A1:E3 => CF => Formula =>
=$A1<>$E1
So A and E are absolute the whole row fill be filled


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Pete,

Am Thu, 3 May 2012 13:06:47 -0700 (PDT) schrieb Pete:


the formula in CF always have to be referred to the active cell


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

P.S. When I see something like "COUNTBLANK(B1:D1)=3" I always think that only row one is checked and CF stops right after that, because the row number is fixed (B1:D1) and not variable (Bx:Dx, with x being a variable representing the current row)

Well, I still have some difficulties understanding how CF works.

If I take the same table as in the earlier example and add
(whis is automatically converted to: $A$1:$E$3 by Excel for any reason) only cells in the first column are marked:
A1 (="x" == E1="x") and C1 (="y" == C3 = "y") and cells E1 and C3 stay without background color, although the formula is still valid.

Hi Pete,

Am Thu, 3 May 2012 13:13:44 -0700 (PDT) schrieb Pete:


Select all cells that should be formatted. Select B1:D & end. B1 now is
active cell. =COUNTBLANK(B1:D1)=3 refers to that cell and for all
selected cells the formula will be adapted. In CF you only see this
starting formula.
What Excel version do you use?


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I am using: MSO 2010 (V 14.0) 64-bit (EN)

By the way, do you have an idea, why my first formula works fine in the sheet, but not in CF?

Hi Pete,

Am Thu, 3 May 2012 13:38:30 -0700 (PDT) schrieb Pete:


in CF the formula has to be reffered to the active cell. You cannot use
INDIRECT in the formula.
Please have a look in both sheets, I hope that will show you the working
of CF:
http://www.claus-busch.de/Excel/Pete.xls


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you very much for this example!


Now I see the link between CF and drag-copying a formula inside the sheet. The "$" just works the same way (fixed row/column).
I think I do now understand the concept, but I will have to try a few things out to see if I really got it.

But it is a real pity that INDIRECT does not work. I use this function very often, since at the time I type the formula I do not exactly know which cells will be affected.
For example one time its 3 cells to the right and 2 cells down (relative to the current). Next time it will be different.
E.g. in cell $M$1 I have the number representing the amount of rows and in $N$1 a number representing the amount of columns. M1=2 (go 2 rows down/up), N1=3 (go 3 rows left/right)

Hi Pete,

Am Thu, 3 May 2012 14:08:06 -0700 (PDT) schrieb Pete:


INDIRECT does not work, because you do not use INDIRECT. The formula
refers to the active cell and will be adapted for all selected cells.
And the formula has nothing to do with the range you will format.
The range that should be formatted you can fix with your selection or
later in CF.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Pete,

Am Thu, 3 May 2012 23:21:58 +0200 schrieb Claus Busch:


or you can change the range with the format painter.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

OK, now I see the point and my misunderstanding.
What I was trying to do was using INDIRECT in an active cell referencing to itself, which is a circular reference. I just tried "=INDIRECT("A"&ROW())" in cell A1 which threw me an error message.
Many things have become clear now thanks to your patience, examples and explanations.