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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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