Multiple-Test Conditional Formatting
 

Hi, everyone!

I have a spreadsheet in which one column (B) contains an item "type", e.g.
"C" or "H". In another column (G), I put a "required" sub-item. I am trying
to conditionally format the cell in column G to do the following:

If Col B = "C" and Col G is blank, make background of Col G red;
Otherwise, make background of Col G normal.

Using normal CF, I can make G's background red if Col B = "C" (formula is),
or I can make it red if Col G is blank (cell value is), but can't seem to
find the trick of combining the two different condition types with an "AND".

I've looked through over a dozen posts here as well as checked out Debra
Dangleish's site and cpearson.com. Can't find anything similar.

Can anyone help?

Thanks,
BruceS

Try this:

Select G1, then,

Formula is,
=AND(B1="C",G1="")

Copy down as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"BruceS" wrote in message
...
Hi, everyone!

I have a spreadsheet in which one column (B) contains an item "type", e.g.
"C" or "H". In another column (G), I put a "required" sub-item. I am
trying
to conditionally format the cell in column G to do the following:

If Col B = "C" and Col G is blank, make background of Col G red;
Otherwise, make background of Col G normal.

Using normal CF, I can make G's background red if Col B = "C" (formula
is),
or I can make it red if Col G is blank (cell value is), but can't seem to
find the trick of combining the two different condition types with an
"AND".

I've looked through over a dozen posts here as well as checked out Debra
Dangleish's site and cpearson.com. Can't find anything similar.

Can anyone help?

Thanks,
BruceS

Bruce,

Select the cells in G2. This CF formula is for where the active (white)
cell of your selection is G2:

=AND(B2="C", G2="")
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"BruceS" wrote in message
...
Hi, everyone!

I have a spreadsheet in which one column (B) contains an item "type", e.g.
"C" or "H". In another column (G), I put a "required" sub-item. I am
trying
to conditionally format the cell in column G to do the following:

If Col B = "C" and Col G is blank, make background of Col G red;
Otherwise, make background of Col G normal.

Using normal CF, I can make G's background red if Col B = "C" (formula
is),
or I can make it red if Col G is blank (cell value is), but can't seem to
find the trick of combining the two different condition types with an
"AND".

I've looked through over a dozen posts here as well as checked out Debra
Dangleish's site and cpearson.com. Can't find anything similar.

Can anyone help?

Thanks,
BruceS

try =and(B1="C",G1="")
I often have to reenter the conditional formating section a couple of times
cause Excel tries to correct what I say and I have to get rid of Quote marks
or absolute references. (Of course I do make enough mistakes, that some of
the reentries are because I wrote it wrong.)

"BruceS" wrote:

Hi, everyone!

I have a spreadsheet in which one column (B) contains an item "type", e.g.
"C" or "H". In another column (G), I put a "required" sub-item. I am trying
to conditionally format the cell in column G to do the following:

If Col B = "C" and Col G is blank, make background of Col G red;
Otherwise, make background of Col G normal.

Using normal CF, I can make G's background red if Col B = "C" (formula is),
or I can make it red if Col G is blank (cell value is), but can't seem to
find the trick of combining the two different condition types with an "AND".

I've looked through over a dozen posts here as well as checked out Debra
Dangleish's site and cpearson.com. Can't find anything similar.

Can anyone help?

Thanks,
BruceS

Thanks, guys!! Worked great.
BruceS

"BruceS" wrote:

Hi, everyone!

I have a spreadsheet in which one column (B) contains an item "type", e.g.
"C" or "H". In another column (G), I put a "required" sub-item. I am trying
to conditionally format the cell in column G to do the following:

If Col B = "C" and Col G is blank, make background of Col G red;
Otherwise, make background of Col G normal.

Using normal CF, I can make G's background red if Col B = "C" (formula is),
or I can make it red if Col G is blank (cell value is), but can't seem to
find the trick of combining the two different condition types with an "AND".

I've looked through over a dozen posts here as well as checked out Debra
Dangleish's site and cpearson.com. Can't find anything similar.

Can anyone help?

Thanks,
BruceS