Conditional format based on entire row
 

I appologize if this question has been asked, I could not find anything close
enough in the archives.

I am not sure that this is possible,but here goes....I have a spreadsheet
that keeps a running daily total for a set of locations. Each day I add
another two columns. "A" is todays data "B" is total to date, then "C" will
be the next day's data and "D" will be that day's total to date. What I want
is to have the entire row turn yellow is there is a blank cell anywhere in
that particular row. This will tell me if there is data missing and that on
day 2 the total to date is not correct. One problem I keep thinking that if
I do set this up, how do I tell it not to look that the empty rows after my
last day? Do I need to set up a range? Will I have to keep changing the
format as I add new days?

Conditional format based on entire row
 

Select the row in question.

Go to format--conditional formatting and enter the following formula:
=ISBLANK(A1) and select the color you would like to appear.
--
Brevity is the soul of wit.


"LisaVH" wrote:

I appologize if this question has been asked, I could not find anything close
enough in the archives.

I am not sure that this is possible,but here goes....I have a spreadsheet
that keeps a running daily total for a set of locations. Each day I add
another two columns. "A" is todays data "B" is total to date, then "C" will
be the next day's data and "D" will be that day's total to date. What I want
is to have the entire row turn yellow is there is a blank cell anywhere in
that particular row. This will tell me if there is data missing and that on
day 2 the total to date is not correct. One problem I keep thinking that if
I do set this up, how do I tell it not to look that the empty rows after my
last day? Do I need to set up a range? Will I have to keep changing the
format as I add new days?

Conditional format based on entire row
 

Won't that only highlight the row if A1 is blank? I want the row to be
highlighted if any cell within that row contains a blank.

"Dave F" wrote:

Select the row in question.

Go to format--conditional formatting and enter the following formula:
=ISBLANK(A1) and select the color you would like to appear.
--
Brevity is the soul of wit.


"LisaVH" wrote:

I appologize if this question has been asked, I could not find anything close
enough in the archives.

I am not sure that this is possible,but here goes....I have a spreadsheet
that keeps a running daily total for a set of locations. Each day I add
another two columns. "A" is todays data "B" is total to date, then "C" will
be the next day's data and "D" will be that day's total to date. What I want
is to have the entire row turn yellow is there is a blank cell anywhere in
that particular row. This will tell me if there is data missing and that on
day 2 the total to date is not correct. One problem I keep thinking that if
I do set this up, how do I tell it not to look that the empty rows after my
last day? Do I need to set up a range? Will I have to keep changing the
format as I add new days?

Conditional format based on entire row
 

Applying that conditional formatting will highlight any cell in the row that
is blank.

Counterintuitive, I know, but that's how conditional formatting with
formulas works....

Dave
--
Brevity is the soul of wit.


"LisaVH" wrote:

Won't that only highlight the row if A1 is blank? I want the row to be
highlighted if any cell within that row contains a blank.

"Dave F" wrote:

Select the row in question.

Go to format--conditional formatting and enter the following formula:
=ISBLANK(A1) and select the color you would like to appear.
--
Brevity is the soul of wit.


"LisaVH" wrote:

I appologize if this question has been asked, I could not find anything close
enough in the archives.

I am not sure that this is possible,but here goes....I have a spreadsheet
that keeps a running daily total for a set of locations. Each day I add
another two columns. "A" is todays data "B" is total to date, then "C" will
be the next day's data and "D" will be that day's total to date. What I want
is to have the entire row turn yellow is there is a blank cell anywhere in
that particular row. This will tell me if there is data missing and that on
day 2 the total to date is not correct. One problem I keep thinking that if
I do set this up, how do I tell it not to look that the empty rows after my
last day? Do I need to set up a range? Will I have to keep changing the
format as I add new days?

Conditional format based on entire row
 

Hi LisaVH,

your column A,C,E... contains hard data one column per day
your column B,D,F...contains your formulated total to date one column per day

if there is a missing data on column A or C or E or on the Last formulated
column, say F..

you want to make the whole row as yellow

try in conditional format
select one row with data (e.g. row(22))
formula is :
=(COLUMNS(22:22)-COUNTBLANK(22:22))<MATCH(LOOKUP(2,1/(22:22<""),(22:22)),22:22,0)
select color yellow as patterns.

there are other shorter formula for this cond. format which can be solicited
in this forum, from others.

regards...

--
*****
birds of the same feather flock together..



"LisaVH" wrote:

I appologize if this question has been asked, I could not find anything close
enough in the archives.

I am not sure that this is possible,but here goes....I have a spreadsheet
that keeps a running daily total for a set of locations. Each day I add
another two columns. "A" is todays data "B" is total to date, then "C" will
be the next day's data and "D" will be that day's total to date. What I want
is to have the entire row turn yellow is there is a blank cell anywhere in
that particular row. This will tell me if there is data missing and that on
day 2 the total to date is not correct. One problem I keep thinking that if
I do set this up, how do I tell it not to look that the empty rows after my
last day? Do I need to set up a range? Will I have to keep changing the
format as I add new days?

Conditional format based on entire row
 

Conditional Formatting

Formula Is: =COUNT($A$1:$Z$1)<COLUMNS($A$1:$Z$1)

Adjust to suit


"LisaVH" wrote:

I appologize if this question has been asked, I could not find anything close
enough in the archives.

I am not sure that this is possible,but here goes....I have a spreadsheet
that keeps a running daily total for a set of locations. Each day I add
another two columns. "A" is todays data "B" is total to date, then "C" will
be the next day's data and "D" will be that day's total to date. What I want
is to have the entire row turn yellow is there is a blank cell anywhere in
that particular row. This will tell me if there is data missing and that on
day 2 the total to date is not correct. One problem I keep thinking that if
I do set this up, how do I tell it not to look that the empty rows after my
last day? Do I need to set up a range? Will I have to keep changing the
format as I add new days?

Conditional format based on entire row
 

This works beautifully. Thank you very much!

LisaVH

"driller" wrote:

Hi LisaVH,

your column A,C,E... contains hard data one column per day
your column B,D,F...contains your formulated total to date one column per day

if there is a missing data on column A or C or E or on the Last formulated
column, say F..

you want to make the whole row as yellow

try in conditional format
select one row with data (e.g. row(22))
formula is :
=(COLUMNS(22:22)-COUNTBLANK(22:22))<MATCH(LOOKUP(2,1/(22:22<""),(22:22)),22:22,0)
select color yellow as patterns.

there are other shorter formula for this cond. format which can be solicited
in this forum, from others.

regards...

--
*****
birds of the same feather flock together..



"LisaVH" wrote:

I appologize if this question has been asked, I could not find anything close
enough in the archives.

I am not sure that this is possible,but here goes....I have a spreadsheet
that keeps a running daily total for a set of locations. Each day I add
another two columns. "A" is todays data "B" is total to date, then "C" will
be the next day's data and "D" will be that day's total to date. What I want
is to have the entire row turn yellow is there is a blank cell anywhere in
that particular row. This will tell me if there is data missing and that on
day 2 the total to date is not correct. One problem I keep thinking that if
I do set this up, how do I tell it not to look that the empty rows after my
last day? Do I need to set up a range? Will I have to keep changing the
format as I add new days?

Conditional format based on entire row
 

I have one more issue. Didn't think of this before, but zero is an
acceptable value in my data. As it stands, if the input data is zero, the
row is highlighted. Is there a sneaky way around this??

LisaVH

"LisaVH" wrote:

This works beautifully. Thank you very much!

LisaVH

"driller" wrote:

Hi LisaVH,

your column A,C,E... contains hard data one column per day
your column B,D,F...contains your formulated total to date one column per day

if there is a missing data on column A or C or E or on the Last formulated
column, say F..

you want to make the whole row as yellow

try in conditional format
select one row with data (e.g. row(22))
formula is :
=(COLUMNS(22:22)-COUNTBLANK(22:22))<MATCH(LOOKUP(2,1/(22:22<""),(22:22)),22:22,0)
select color yellow as patterns.

there are other shorter formula for this cond. format which can be solicited
in this forum, from others.

regards...

--
*****
birds of the same feather flock together..



"LisaVH" wrote:

I appologize if this question has been asked, I could not find anything close
enough in the archives.

I am not sure that this is possible,but here goes....I have a spreadsheet
that keeps a running daily total for a set of locations. Each day I add
another two columns. "A" is todays data "B" is total to date, then "C" will
be the next day's data and "D" will be that day's total to date. What I want
is to have the entire row turn yellow is there is a blank cell anywhere in
that particular row. This will tell me if there is data missing and that on
day 2 the total to date is not correct. One problem I keep thinking that if
I do set this up, how do I tell it not to look that the empty rows after my
last day? Do I need to set up a range? Will I have to keep changing the
format as I add new days?

Conditional format based on entire row
 

you're welcome Lisa,
maybe this mixed with OR equal to zero
=OR(COUNTIF(22:22,"=0"),(COLUMNS(22:22)-COUNTBLANK(22:22))<MATCH(LOOKUP(2,1/(22:22<""),(22:22)),22:22,0))
--
*****
birds of the same feather flock together..



"LisaVH" wrote:

I have one more issue. Didn't think of this before, but zero is an
acceptable value in my data. As it stands, if the input data is zero, the
row is highlighted. Is there a sneaky way around this??

LisaVH

"LisaVH" wrote:

This works beautifully. Thank you very much!

LisaVH

"driller" wrote:

Hi LisaVH,

your column A,C,E... contains hard data one column per day
your column B,D,F...contains your formulated total to date one column per day

if there is a missing data on column A or C or E or on the Last formulated
column, say F..

you want to make the whole row as yellow

try in conditional format
select one row with data (e.g. row(22))
formula is :
=(COLUMNS(22:22)-COUNTBLANK(22:22))<MATCH(LOOKUP(2,1/(22:22<""),(22:22)),22:22,0)
select color yellow as patterns.

there are other shorter formula for this cond. format which can be solicited
in this forum, from others.

regards...

--
*****
birds of the same feather flock together..



"LisaVH" wrote:

I appologize if this question has been asked, I could not find anything close
enough in the archives.

I am not sure that this is possible,but here goes....I have a spreadsheet
that keeps a running daily total for a set of locations. Each day I add
another two columns. "A" is todays data "B" is total to date, then "C" will
be the next day's data and "D" will be that day's total to date. What I want
is to have the entire row turn yellow is there is a blank cell anywhere in
that particular row. This will tell me if there is data missing and that on
day 2 the total to date is not correct. One problem I keep thinking that if
I do set this up, how do I tell it not to look that the empty rows after my
last day? Do I need to set up a range? Will I have to keep changing the
format as I add new days?