YACFQ - Yet Another Conditional Formatting Question...

[Pheasant Plucker®]

I'll never get the hang of this...:-(

I had a similar problem recently that was solved in this ng (Thanks Dave:-)
but have a related problem that I thought I could solve by applying the same
logic - alas it was not meant to be for me :-(

I want to use Conditional Formatting to achieve the following - if the value
in Cell H15 is lower than 75 then colour it red.

This I can achieve quite easily by using the following in Condition 1:

If Cell Value is Less Than 75 then Format Pattern Red

However I do not want the cell to be coloured Red if there is no value in
there at all - I only want to be able to turn the cell red after I have
input the value and that value is less than 75

Obviously I need to test for input in H15 prior to checking if it is below
75 and invoking the colour change if it is - leaving no change if it has no
value or is 75 or above.

I have tried using a COUNTA but cannot seem to get it to work.

Can some kind soul help me out on this one (again) please?

--
Thanks & regards,
-pp-

[Biff]

Hi!

Just a little tweak is all you need.

Use the Formula Is option
Formula is: =AND(ISNUMBER(H15),H15<75)

Biff

"Pheasant PluckerŪ" <[email protected]> wrote in message
news:[email protected]...
> I'll never get the hang of this...:-(
>
> I had a similar problem recently that was solved in this ng (Thanks
> Dave:-)
> but have a related problem that I thought I could solve by applying the
> same
> logic - alas it was not meant to be for me :-(
>
> I want to use Conditional Formatting to achieve the following - if the
> value
> in Cell H15 is lower than 75 then colour it red.
>
> This I can achieve quite easily by using the following in Condition 1:
>
> If Cell Value is Less Than 75 then Format Pattern Red
>
> However I do not want the cell to be coloured Red if there is no value in
> there at all - I only want to be able to turn the cell red after I have
> input the value and that value is less than 75
>
> Obviously I need to test for input in H15 prior to checking if it is below
> 75 and invoking the colour change if it is - leaving no change if it has
> no
> value or is 75 or above.
>
> I have tried using a COUNTA but cannot seem to get it to work.
>
> Can some kind soul help me out on this one (again) please?
>
> --
> Thanks & regards,
> -pp-
>
>

[Pete_UK]

Change the format condition to "Formua Is" rather than "Cell Value Is"
and enter this formula;

=AND(H15>0,H15<75)

You can still have the same red background format.

Hope this helps.

Pete

[Pheasant Plucker®]

Thanks for the quick reply Biff.

Did I miss something here or was my original post not as clear as it should
have been...

I tried your formula but even though at rest the cell is clear (good) if I
enter 75 or above the Cell is still red (bad)?

I only want it to change to Red if below 75 - if 75 or above I do not want
to colour it - just leave it as a normal cell...

Thanks for your patience!

Regards,
-=pp=-


"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Just a little tweak is all you need.
>
> Use the Formula Is option
> Formula is: =AND(ISNUMBER(H15),H15<75)
>
> Biff
>
> "Pheasant PluckerŪ" <[email protected]> wrote in message
> news:[email protected]...
> > I'll never get the hang of this...:-(
> >
> > I had a similar problem recently that was solved in this ng (Thanks
> > Dave:-)
> > but have a related problem that I thought I could solve by applying the
> > same
> > logic - alas it was not meant to be for me :-(
> >
> > I want to use Conditional Formatting to achieve the following - if the
> > value
> > in Cell H15 is lower than 75 then colour it red.
> >
> > This I can achieve quite easily by using the following in Condition 1:
> >
> > If Cell Value is Less Than 75 then Format Pattern Red
> >
> > However I do not want the cell to be coloured Red if there is no value
in
> > there at all - I only want to be able to turn the cell red after I have
> > input the value and that value is less than 75
> >
> > Obviously I need to test for input in H15 prior to checking if it is
below
> > 75 and invoking the colour change if it is - leaving no change if it has
> > no
> > value or is 75 or above.
> >
> > I have tried using a COUNTA but cannot seem to get it to work.
> >
> > Can some kind soul help me out on this one (again) please?
> >
> > --
> > Thanks & regards,
> > -pp-
> >
> >
>
>

[Pheasant Plucker®]

Thanks for the quick reply Pete,

I must have missed something here as both suggestions from yourself & Biff
did not work for me...

I tried your formula but even though at rest the cell is clear (good) if I
enter 75 or above the Cell is still red (bad)?

I only want it to change to Red if below 75 - if 75 or above I do not want
to colour it - just leave it as a normal cell...

Thanks for your patience also!

Regards,
-=pp=-


"Pete_UK" <[email protected]> wrote in message
news:[email protected]...
> Change the format condition to "Formua Is" rather than "Cell Value Is"
> and enter this formula;
>
> =AND(H15>0,H15<75)
>
> You can still have the same red background format.
>
> Hope this helps.
>
> Pete
>

[Biff]

Hmmm.....

Do you have any other conditions? Condition 2 and/or condition 3?

If this is the ONLY condition it should work!

Biff

"Pheasant PluckerŪ" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the quick reply Biff.
>
> Did I miss something here or was my original post not as clear as it
> should
> have been...
>
> I tried your formula but even though at rest the cell is clear (good) if I
> enter 75 or above the Cell is still red (bad)?
>
> I only want it to change to Red if below 75 - if 75 or above I do not want
> to colour it - just leave it as a normal cell...
>
> Thanks for your patience!
>
> Regards,
> -=pp=-
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hi!
>>
>> Just a little tweak is all you need.
>>
>> Use the Formula Is option
>> Formula is: =AND(ISNUMBER(H15),H15<75)
>>
>> Biff
>>
>> "Pheasant PluckerŪ" <[email protected]> wrote in message
>> news:[email protected]...
>> > I'll never get the hang of this...:-(
>> >
>> > I had a similar problem recently that was solved in this ng (Thanks
>> > Dave:-)
>> > but have a related problem that I thought I could solve by applying the
>> > same
>> > logic - alas it was not meant to be for me :-(
>> >
>> > I want to use Conditional Formatting to achieve the following - if the
>> > value
>> > in Cell H15 is lower than 75 then colour it red.
>> >
>> > This I can achieve quite easily by using the following in Condition 1:
>> >
>> > If Cell Value is Less Than 75 then Format Pattern Red
>> >
>> > However I do not want the cell to be coloured Red if there is no value
> in
>> > there at all - I only want to be able to turn the cell red after I have
>> > input the value and that value is less than 75
>> >
>> > Obviously I need to test for input in H15 prior to checking if it is
> below
>> > 75 and invoking the colour change if it is - leaving no change if it
>> > has
>> > no
>> > value or is 75 or above.
>> >
>> > I have tried using a COUNTA but cannot seem to get it to work.
>> >
>> > Can some kind soul help me out on this one (again) please?
>> >
>> > --
>> > Thanks & regards,
>> > -pp-
>> >
>> >
>>
>>
>
>

[Pheasant Plucker®]

Hi Biff,

No...no other conditions at all...

No formula in the cell either...wait a minute...would it matter if the
Format Cells/Number is set for a Percentage?
..
..
..
..
Yup...that's it...if I set it to a number it works as you guys thought it
would.

OK...how's about my setting it to a number instead of a percentage?

Or what should the formula be if I left it as a percentage?

<mind racing away here>

What would the Conditional Formatting be if I wanted H15 to be left
uncoloured if there is no value, red if the value is below 75 and green if
75 or above?

Sorry for all the questions...

Thanks & regards,
-=pp=-



"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hmmm.....
>
> Do you have any other conditions? Condition 2 and/or condition 3?
>
> If this is the ONLY condition it should work!
>
> Biff
>
> "Pheasant PluckerŪ" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the quick reply Biff.
> >
> > Did I miss something here or was my original post not as clear as it
> > should
> > have been...
> >
> > I tried your formula but even though at rest the cell is clear (good) if
I
> > enter 75 or above the Cell is still red (bad)?
> >
> > I only want it to change to Red if below 75 - if 75 or above I do not
want
> > to colour it - just leave it as a normal cell...
> >
> > Thanks for your patience!
> >
> > Regards,
> > -=pp=-
> >
> >
> > "Biff" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi!
> >>
> >> Just a little tweak is all you need.
> >>
> >> Use the Formula Is option
> >> Formula is: =AND(ISNUMBER(H15),H15<75)
> >>
> >> Biff
> >>
> >> "Pheasant PluckerŪ" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > I'll never get the hang of this...:-(
> >> >
> >> > I had a similar problem recently that was solved in this ng (Thanks
> >> > Dave:-)
> >> > but have a related problem that I thought I could solve by applying
the
> >> > same
> >> > logic - alas it was not meant to be for me :-(
> >> >
> >> > I want to use Conditional Formatting to achieve the following - if
the
> >> > value
> >> > in Cell H15 is lower than 75 then colour it red.
> >> >
> >> > This I can achieve quite easily by using the following in Condition
1:
> >> >
> >> > If Cell Value is Less Than 75 then Format Pattern Red
> >> >
> >> > However I do not want the cell to be coloured Red if there is no
value
> > in
> >> > there at all - I only want to be able to turn the cell red after I
have
> >> > input the value and that value is less than 75
> >> >
> >> > Obviously I need to test for input in H15 prior to checking if it is
> > below
> >> > 75 and invoking the colour change if it is - leaving no change if it
> >> > has
> >> > no
> >> > value or is 75 or above.
> >> >
> >> > I have tried using a COUNTA but cannot seem to get it to work.
> >> >
> >> > Can some kind soul help me out on this one (again) please?
> >> >
> >> > --
> >> > Thanks & regards,
> >> > -pp-
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Biff]

>would it matter if the Format Cells/Number is set for a Percentage?

Bingo!

So, change the formula to:

=AND(ISNUMBER(H15),H15<0.75)

Biff

"Pheasant PluckerŪ" <[email protected]> wrote in message
news:%[email protected]...
> Hi Biff,
>
> No...no other conditions at all...
>
> No formula in the cell either...wait a minute...would it matter if the
> Format Cells/Number is set for a Percentage?
> .
> .
> .
> .
> Yup...that's it...if I set it to a number it works as you guys thought it
> would.
>
> OK...how's about my setting it to a number instead of a percentage?
>
> Or what should the formula be if I left it as a percentage?
>
> <mind racing away here>
>
> What would the Conditional Formatting be if I wanted H15 to be left
> uncoloured if there is no value, red if the value is below 75 and green if
> 75 or above?
>
> Sorry for all the questions...
>
> Thanks & regards,
> -=pp=-
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hmmm.....
>>
>> Do you have any other conditions? Condition 2 and/or condition 3?
>>
>> If this is the ONLY condition it should work!
>>
>> Biff
>>
>> "Pheasant PluckerŪ" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thanks for the quick reply Biff.
>> >
>> > Did I miss something here or was my original post not as clear as it
>> > should
>> > have been...
>> >
>> > I tried your formula but even though at rest the cell is clear (good)
>> > if
> I
>> > enter 75 or above the Cell is still red (bad)?
>> >
>> > I only want it to change to Red if below 75 - if 75 or above I do not
> want
>> > to colour it - just leave it as a normal cell...
>> >
>> > Thanks for your patience!
>> >
>> > Regards,
>> > -=pp=-
>> >
>> >
>> > "Biff" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi!
>> >>
>> >> Just a little tweak is all you need.
>> >>
>> >> Use the Formula Is option
>> >> Formula is: =AND(ISNUMBER(H15),H15<75)
>> >>
>> >> Biff
>> >>
>> >> "Pheasant PluckerŪ" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > I'll never get the hang of this...:-(
>> >> >
>> >> > I had a similar problem recently that was solved in this ng (Thanks
>> >> > Dave:-)
>> >> > but have a related problem that I thought I could solve by applying
> the
>> >> > same
>> >> > logic - alas it was not meant to be for me :-(
>> >> >
>> >> > I want to use Conditional Formatting to achieve the following - if
> the
>> >> > value
>> >> > in Cell H15 is lower than 75 then colour it red.
>> >> >
>> >> > This I can achieve quite easily by using the following in Condition
> 1:
>> >> >
>> >> > If Cell Value is Less Than 75 then Format Pattern Red
>> >> >
>> >> > However I do not want the cell to be coloured Red if there is no
> value
>> > in
>> >> > there at all - I only want to be able to turn the cell red after I
> have
>> >> > input the value and that value is less than 75
>> >> >
>> >> > Obviously I need to test for input in H15 prior to checking if it is
>> > below
>> >> > 75 and invoking the colour change if it is - leaving no change if it
>> >> > has
>> >> > no
>> >> > value or is 75 or above.
>> >> >
>> >> > I have tried using a COUNTA but cannot seem to get it to work.
>> >> >
>> >> > Can some kind soul help me out on this one (again) please?
>> >> >
>> >> > --
>> >> > Thanks & regards,
>> >> > -pp-
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Biff]

Oops!

Missed this portion:

>What would the Conditional Formatting be if I wanted H15 to be left
>uncoloured if there is no value, red if the value is below 75 and green if
>75 or above?

Condition 1
Formula is: =AND(ISNUMBER(H15),H15<0.75)
Set color to RED

Condition 2
Formula is: =AND(ISNUMBER(H15),H15>=0.75)
Set color to GREEN

Biff

"Pheasant PluckerŪ" <[email protected]> wrote in message
news:%[email protected]...
> Hi Biff,
>
> No...no other conditions at all...
>
> No formula in the cell either...wait a minute...would it matter if the
> Format Cells/Number is set for a Percentage?
> .
> .
> .
> .
> Yup...that's it...if I set it to a number it works as you guys thought it
> would.
>
> OK...how's about my setting it to a number instead of a percentage?
>
> Or what should the formula be if I left it as a percentage?
>
> <mind racing away here>
>
> What would the Conditional Formatting be if I wanted H15 to be left
> uncoloured if there is no value, red if the value is below 75 and green if
> 75 or above?
>
> Sorry for all the questions...
>
> Thanks & regards,
> -=pp=-
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hmmm.....
>>
>> Do you have any other conditions? Condition 2 and/or condition 3?
>>
>> If this is the ONLY condition it should work!
>>
>> Biff
>>
>> "Pheasant PluckerŪ" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thanks for the quick reply Biff.
>> >
>> > Did I miss something here or was my original post not as clear as it
>> > should
>> > have been...
>> >
>> > I tried your formula but even though at rest the cell is clear (good)
>> > if
> I
>> > enter 75 or above the Cell is still red (bad)?
>> >
>> > I only want it to change to Red if below 75 - if 75 or above I do not
> want
>> > to colour it - just leave it as a normal cell...
>> >
>> > Thanks for your patience!
>> >
>> > Regards,
>> > -=pp=-
>> >
>> >
>> > "Biff" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi!
>> >>
>> >> Just a little tweak is all you need.
>> >>
>> >> Use the Formula Is option
>> >> Formula is: =AND(ISNUMBER(H15),H15<75)
>> >>
>> >> Biff
>> >>
>> >> "Pheasant PluckerŪ" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > I'll never get the hang of this...:-(
>> >> >
>> >> > I had a similar problem recently that was solved in this ng (Thanks
>> >> > Dave:-)
>> >> > but have a related problem that I thought I could solve by applying
> the
>> >> > same
>> >> > logic - alas it was not meant to be for me :-(
>> >> >
>> >> > I want to use Conditional Formatting to achieve the following - if
> the
>> >> > value
>> >> > in Cell H15 is lower than 75 then colour it red.
>> >> >
>> >> > This I can achieve quite easily by using the following in Condition
> 1:
>> >> >
>> >> > If Cell Value is Less Than 75 then Format Pattern Red
>> >> >
>> >> > However I do not want the cell to be coloured Red if there is no
> value
>> > in
>> >> > there at all - I only want to be able to turn the cell red after I
> have
>> >> > input the value and that value is less than 75
>> >> >
>> >> > Obviously I need to test for input in H15 prior to checking if it is
>> > below
>> >> > 75 and invoking the colour change if it is - leaving no change if it
>> >> > has
>> >> > no
>> >> > value or is 75 or above.
>> >> >
>> >> > I have tried using a COUNTA but cannot seem to get it to work.
>> >> >
>> >> > Can some kind soul help me out on this one (again) please?
>> >> >
>> >> > --
>> >> > Thanks & regards,
>> >> > -pp-
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Pheasant Plucker®]

Thanks Biff,

I have moved away from the Percentage and formatted the cell as a number.

I see you use ISNUMBER in your formula

I currently have;

Condition 1
Formula Is =AND(H15>0,H15<75)
Pattern RED

Condition 2
Cell Value Is greater than or equal To 75
Pattern GREEN

Does that have any impact upon anything else or would the two ISNUMBER
formulae be the 'correct' way to go?

I only used AND as it was shorter, easier to type and reduced the chance of
me making a mistake!

One last question if I may...as I use H15 & H16 to input the scores of what
I call Pre and Post Test results is there a way to highlight H16 if it turns
out to be lower than the score in H15?

Theoretically the Post Test score would usually be higher but it would be
good to flag it if it turns out to be lower than the Pre Test score.

Would this be complicated to achieve?

Thanks & regards,
-=pp=-


"Biff" <[email protected]> wrote in message
news:[email protected]...
> Oops!
>
> Missed this portion:
>
> >What would the Conditional Formatting be if I wanted H15 to be left
> >uncoloured if there is no value, red if the value is below 75 and green
if
> >75 or above?
>
> Condition 1
> Formula is: =AND(ISNUMBER(H15),H15<0.75)
> Set color to RED
>
> Condition 2
> Formula is: =AND(ISNUMBER(H15),H15>=0.75)
> Set color to GREEN
>
> Biff
>
> "Pheasant PluckerŪ" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi Biff,
> >
> > No...no other conditions at all...
> >
> > No formula in the cell either...wait a minute...would it matter if the
> > Format Cells/Number is set for a Percentage?
> > .
> > .
> > .
> > .
> > Yup...that's it...if I set it to a number it works as you guys thought
it
> > would.
> >
> > OK...how's about my setting it to a number instead of a percentage?
> >
> > Or what should the formula be if I left it as a percentage?
> >
> > <mind racing away here>
> >
> > What would the Conditional Formatting be if I wanted H15 to be left
> > uncoloured if there is no value, red if the value is below 75 and green
if
> > 75 or above?
> >
> > Sorry for all the questions...
> >
> > Thanks & regards,
> > -=pp=-
> >
> >
> >
> > "Biff" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hmmm.....
> >>
> >> Do you have any other conditions? Condition 2 and/or condition 3?
> >>
> >> If this is the ONLY condition it should work!
> >>
> >> Biff
> >>
> >> "Pheasant PluckerŪ" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Thanks for the quick reply Biff.
> >> >
> >> > Did I miss something here or was my original post not as clear as it
> >> > should
> >> > have been...
> >> >
> >> > I tried your formula but even though at rest the cell is clear (good)
> >> > if
> > I
> >> > enter 75 or above the Cell is still red (bad)?
> >> >
> >> > I only want it to change to Red if below 75 - if 75 or above I do not
> > want
> >> > to colour it - just leave it as a normal cell...
> >> >
> >> > Thanks for your patience!
> >> >
> >> > Regards,
> >> > -=pp=-
> >> >
> >> >
> >> > "Biff" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Hi!
> >> >>
> >> >> Just a little tweak is all you need.
> >> >>
> >> >> Use the Formula Is option
> >> >> Formula is: =AND(ISNUMBER(H15),H15<75)
> >> >>
> >> >> Biff
> >> >>
> >> >> "Pheasant PluckerŪ" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > I'll never get the hang of this...:-(
> >> >> >
> >> >> > I had a similar problem recently that was solved in this ng
(Thanks
> >> >> > Dave:-)
> >> >> > but have a related problem that I thought I could solve by
applying
> > the
> >> >> > same
> >> >> > logic - alas it was not meant to be for me :-(
> >> >> >
> >> >> > I want to use Conditional Formatting to achieve the following - if
> > the
> >> >> > value
> >> >> > in Cell H15 is lower than 75 then colour it red.
> >> >> >
> >> >> > This I can achieve quite easily by using the following in
Condition
> > 1:
> >> >> >
> >> >> > If Cell Value is Less Than 75 then Format Pattern Red
> >> >> >
> >> >> > However I do not want the cell to be coloured Red if there is no
> > value
> >> > in
> >> >> > there at all - I only want to be able to turn the cell red after I
> > have
> >> >> > input the value and that value is less than 75
> >> >> >
> >> >> > Obviously I need to test for input in H15 prior to checking if it
is
> >> > below
> >> >> > 75 and invoking the colour change if it is - leaving no change if
it
> >> >> > has
> >> >> > no
> >> >> > value or is 75 or above.
> >> >> >
> >> >> > I have tried using a COUNTA but cannot seem to get it to work.
> >> >> >
> >> >> > Can some kind soul help me out on this one (again) please?
> >> >> >
> >> >> > --
> >> >> > Thanks & regards,
> >> >> > -pp-
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Mark Lincoln]

Choose Formula Is and type in the following:

=AND(NOT(ISBLANK(H15)),H15<75)

[Pete_UK]

You and Biff seem to have got to the bottom of this - sorry I wasn't
around (live football on TV!)

Pete

[Pheasant Plucker®]

Hey Pete,

Priorities...I am the sad one! ;^)

Kind regards,
-=pp=-


"Pete_UK" <[email protected]> wrote in message
news:[email protected]...
> You and Biff seem to have got to the bottom of this - sorry I wasn't
> around (live football on TV!)
>
> Pete
>

[Biff]

snip>>>>>>

Condition 1
Formula Is =AND(H15>0,H15<75)
Pattern RED

Condition 2
Cell Value Is greater than or equal To 75
Pattern GREEN

Does that have any impact upon anything else or would the two ISNUMBER
formulae be the 'correct' way to go?

snip>>>>>>

Try entering a text value in cell H15 and see what happens.

I use Isnumber to add a little more robutness. Isnumber eliminates the empty
cell problem and the TEXT problem at the same time. I always use the
"Formula is" option. Just my personal preference, but writing a formula
gives you more versatility.

> One last question if I may...as I use H15 & H16 to input the scores of
> what
> I call Pre and Post Test results is there a way to highlight H16 if it
> turns
> out to be lower than the score in H15?

For H16:

Formula is: =AND(COUNT(H15,H16)=2,H16<H15)

Using Count makes sure both cells are not empty and both cells contain
numbers and it's shorter than:

=AND(ISNUMBER(H15),ISNUMBER(H16),H16<H15)
=AND(COUNT(H15,H16)=2,H16<H15)

Biff

"Pheasant PluckerŪ" <[email protected]> wrote in message
news:[email protected]...
> Thanks Biff,
>
> I have moved away from the Percentage and formatted the cell as a number.
>
> I see you use ISNUMBER in your formula
>
> I currently have;
>
> Condition 1
> Formula Is =AND(H15>0,H15<75)
> Pattern RED
>
> Condition 2
> Cell Value Is greater than or equal To 75
> Pattern GREEN
>
> Does that have any impact upon anything else or would the two ISNUMBER
> formulae be the 'correct' way to go?
>
> I only used AND as it was shorter, easier to type and reduced the chance
> of
> me making a mistake!
>
> One last question if I may...as I use H15 & H16 to input the scores of
> what
> I call Pre and Post Test results is there a way to highlight H16 if it
> turns
> out to be lower than the score in H15?
>
> Theoretically the Post Test score would usually be higher but it would be
> good to flag it if it turns out to be lower than the Pre Test score.
>
> Would this be complicated to achieve?
>
> Thanks & regards,
> -=pp=-
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Oops!
>>
>> Missed this portion:
>>
>> >What would the Conditional Formatting be if I wanted H15 to be left
>> >uncoloured if there is no value, red if the value is below 75 and green
> if
>> >75 or above?
>>
>> Condition 1
>> Formula is: =AND(ISNUMBER(H15),H15<0.75)
>> Set color to RED
>>
>> Condition 2
>> Formula is: =AND(ISNUMBER(H15),H15>=0.75)
>> Set color to GREEN
>>
>> Biff
>>
>> "Pheasant PluckerŪ" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Hi Biff,
>> >
>> > No...no other conditions at all...
>> >
>> > No formula in the cell either...wait a minute...would it matter if the
>> > Format Cells/Number is set for a Percentage?
>> > .
>> > .
>> > .
>> > .
>> > Yup...that's it...if I set it to a number it works as you guys thought
> it
>> > would.
>> >
>> > OK...how's about my setting it to a number instead of a percentage?
>> >
>> > Or what should the formula be if I left it as a percentage?
>> >
>> > <mind racing away here>
>> >
>> > What would the Conditional Formatting be if I wanted H15 to be left
>> > uncoloured if there is no value, red if the value is below 75 and green
> if
>> > 75 or above?
>> >
>> > Sorry for all the questions...
>> >
>> > Thanks & regards,
>> > -=pp=-
>> >
>> >
>> >
>> > "Biff" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hmmm.....
>> >>
>> >> Do you have any other conditions? Condition 2 and/or condition 3?
>> >>
>> >> If this is the ONLY condition it should work!
>> >>
>> >> Biff
>> >>
>> >> "Pheasant PluckerŪ" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Thanks for the quick reply Biff.
>> >> >
>> >> > Did I miss something here or was my original post not as clear as it
>> >> > should
>> >> > have been...
>> >> >
>> >> > I tried your formula but even though at rest the cell is clear
>> >> > (good)
>> >> > if
>> > I
>> >> > enter 75 or above the Cell is still red (bad)?
>> >> >
>> >> > I only want it to change to Red if below 75 - if 75 or above I do
>> >> > not
>> > want
>> >> > to colour it - just leave it as a normal cell...
>> >> >
>> >> > Thanks for your patience!
>> >> >
>> >> > Regards,
>> >> > -=pp=-
>> >> >
>> >> >
>> >> > "Biff" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Hi!
>> >> >>
>> >> >> Just a little tweak is all you need.
>> >> >>
>> >> >> Use the Formula Is option
>> >> >> Formula is: =AND(ISNUMBER(H15),H15<75)
>> >> >>
>> >> >> Biff
>> >> >>
>> >> >> "Pheasant PluckerŪ" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > I'll never get the hang of this...:-(
>> >> >> >
>> >> >> > I had a similar problem recently that was solved in this ng
> (Thanks
>> >> >> > Dave:-)
>> >> >> > but have a related problem that I thought I could solve by
> applying
>> > the
>> >> >> > same
>> >> >> > logic - alas it was not meant to be for me :-(
>> >> >> >
>> >> >> > I want to use Conditional Formatting to achieve the following -
>> >> >> > if
>> > the
>> >> >> > value
>> >> >> > in Cell H15 is lower than 75 then colour it red.
>> >> >> >
>> >> >> > This I can achieve quite easily by using the following in
> Condition
>> > 1:
>> >> >> >
>> >> >> > If Cell Value is Less Than 75 then Format Pattern Red
>> >> >> >
>> >> >> > However I do not want the cell to be coloured Red if there is no
>> > value
>> >> > in
>> >> >> > there at all - I only want to be able to turn the cell red after
>> >> >> > I
>> > have
>> >> >> > input the value and that value is less than 75
>> >> >> >
>> >> >> > Obviously I need to test for input in H15 prior to checking if it
> is
>> >> > below
>> >> >> > 75 and invoking the colour change if it is - leaving no change if
> it
>> >> >> > has
>> >> >> > no
>> >> >> > value or is 75 or above.
>> >> >> >
>> >> >> > I have tried using a COUNTA but cannot seem to get it to work.
>> >> >> >
>> >> >> > Can some kind soul help me out on this one (again) please?
>> >> >> >
>> >> >> > --
>> >> >> > Thanks & regards,
>> >> >> > -pp-
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Pheasant Plucker®]

Thanks for your patience and the great help Biff.

I have changed the CF to use ISNUMBER in both conditions in H15 and have
plugged in your Formula Is in the CF for H16 and al works well.

Thanks again & kind regards,
-=pp=-


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> snip>>>>>>
>
> Condition 1
> Formula Is =AND(H15>0,H15<75)
> Pattern RED
>
> Condition 2
> Cell Value Is greater than or equal To 75
> Pattern GREEN
>
> Does that have any impact upon anything else or would the two ISNUMBER
> formulae be the 'correct' way to go?
>
> snip>>>>>>
>
> Try entering a text value in cell H15 and see what happens.
>
> I use Isnumber to add a little more robutness. Isnumber eliminates the
empty
> cell problem and the TEXT problem at the same time. I always use the
> "Formula is" option. Just my personal preference, but writing a formula
> gives you more versatility.
>
> > One last question if I may...as I use H15 & H16 to input the scores of
> > what
> > I call Pre and Post Test results is there a way to highlight H16 if it
> > turns
> > out to be lower than the score in H15?
>
> For H16:
>
> Formula is: =AND(COUNT(H15,H16)=2,H16<H15)
>
> Using Count makes sure both cells are not empty and both cells contain
> numbers and it's shorter than:
>
> =AND(ISNUMBER(H15),ISNUMBER(H16),H16<H15)
> =AND(COUNT(H15,H16)=2,H16<H15)
>
> Biff
>
> "Pheasant PluckerŪ" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Biff,
> >
> > I have moved away from the Percentage and formatted the cell as a
number.
> >
> > I see you use ISNUMBER in your formula
> >
> > I currently have;
> >
> > Condition 1
> > Formula Is =AND(H15>0,H15<75)
> > Pattern RED
> >
> > Condition 2
> > Cell Value Is greater than or equal To 75
> > Pattern GREEN
> >
> > Does that have any impact upon anything else or would the two ISNUMBER
> > formulae be the 'correct' way to go?
> >
> > I only used AND as it was shorter, easier to type and reduced the chance
> > of
> > me making a mistake!
> >
> > One last question if I may...as I use H15 & H16 to input the scores of
> > what
> > I call Pre and Post Test results is there a way to highlight H16 if it
> > turns
> > out to be lower than the score in H15?
> >
> > Theoretically the Post Test score would usually be higher but it would
be
> > good to flag it if it turns out to be lower than the Pre Test score.
> >
> > Would this be complicated to achieve?
> >
> > Thanks & regards,
> > -=pp=-
> >
> >
> > "Biff" <[email protected]> wrote in message
> > news:[email protected]...
> >> Oops!
> >>
> >> Missed this portion:
> >>
> >> >What would the Conditional Formatting be if I wanted H15 to be left
> >> >uncoloured if there is no value, red if the value is below 75 and
green
> > if
> >> >75 or above?
> >>
> >> Condition 1
> >> Formula is: =AND(ISNUMBER(H15),H15<0.75)
> >> Set color to RED
> >>
> >> Condition 2
> >> Formula is: =AND(ISNUMBER(H15),H15>=0.75)
> >> Set color to GREEN
> >>
> >> Biff
> >>
> >> "Pheasant PluckerŪ" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Hi Biff,
> >> >
> >> > No...no other conditions at all...
> >> >
> >> > No formula in the cell either...wait a minute...would it matter if
the
> >> > Format Cells/Number is set for a Percentage?
> >> > .
> >> > .
> >> > .
> >> > .
> >> > Yup...that's it...if I set it to a number it works as you guys
thought
> > it
> >> > would.
> >> >
> >> > OK...how's about my setting it to a number instead of a percentage?
> >> >
> >> > Or what should the formula be if I left it as a percentage?
> >> >
> >> > <mind racing away here>
> >> >
> >> > What would the Conditional Formatting be if I wanted H15 to be left
> >> > uncoloured if there is no value, red if the value is below 75 and
green
> > if
> >> > 75 or above?
> >> >
> >> > Sorry for all the questions...
> >> >
> >> > Thanks & regards,
> >> > -=pp=-
> >> >
> >> >
> >> >
> >> > "Biff" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Hmmm.....
> >> >>
> >> >> Do you have any other conditions? Condition 2 and/or condition 3?
> >> >>
> >> >> If this is the ONLY condition it should work!
> >> >>
> >> >> Biff
> >> >>
> >> >> "Pheasant PluckerŪ" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Thanks for the quick reply Biff.
> >> >> >
> >> >> > Did I miss something here or was my original post not as clear as
it
> >> >> > should
> >> >> > have been...
> >> >> >
> >> >> > I tried your formula but even though at rest the cell is clear
> >> >> > (good)
> >> >> > if
> >> > I
> >> >> > enter 75 or above the Cell is still red (bad)?
> >> >> >
> >> >> > I only want it to change to Red if below 75 - if 75 or above I do
> >> >> > not
> >> > want
> >> >> > to colour it - just leave it as a normal cell...
> >> >> >
> >> >> > Thanks for your patience!
> >> >> >
> >> >> > Regards,
> >> >> > -=pp=-
> >> >> >
> >> >> >
> >> >> > "Biff" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> Hi!
> >> >> >>
> >> >> >> Just a little tweak is all you need.
> >> >> >>
> >> >> >> Use the Formula Is option
> >> >> >> Formula is: =AND(ISNUMBER(H15),H15<75)
> >> >> >>
> >> >> >> Biff
> >> >> >>
> >> >> >> "Pheasant PluckerŪ" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >> > I'll never get the hang of this...:-(
> >> >> >> >
> >> >> >> > I had a similar problem recently that was solved in this ng
> > (Thanks
> >> >> >> > Dave:-)
> >> >> >> > but have a related problem that I thought I could solve by
> > applying
> >> > the
> >> >> >> > same
> >> >> >> > logic - alas it was not meant to be for me :-(
> >> >> >> >
> >> >> >> > I want to use Conditional Formatting to achieve the following -
> >> >> >> > if
> >> > the
> >> >> >> > value
> >> >> >> > in Cell H15 is lower than 75 then colour it red.
> >> >> >> >
> >> >> >> > This I can achieve quite easily by using the following in
> > Condition
> >> > 1:
> >> >> >> >
> >> >> >> > If Cell Value is Less Than 75 then Format Pattern Red
> >> >> >> >
> >> >> >> > However I do not want the cell to be coloured Red if there is
no
> >> > value
> >> >> > in
> >> >> >> > there at all - I only want to be able to turn the cell red
after
> >> >> >> > I
> >> > have
> >> >> >> > input the value and that value is less than 75
> >> >> >> >
> >> >> >> > Obviously I need to test for input in H15 prior to checking if
it
> > is
> >> >> > below
> >> >> >> > 75 and invoking the colour change if it is - leaving no change
if
> > it
> >> >> >> > has
> >> >> >> > no
> >> >> >> > value or is 75 or above.
> >> >> >> >
> >> >> >> > I have tried using a COUNTA but cannot seem to get it to work.
> >> >> >> >
> >> >> >> > Can some kind soul help me out on this one (again) please?
> >> >> >> >
> >> >> >> > --
> >> >> >> > Thanks & regards,
> >> >> >> > -pp-
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[Pheasant Plucker®]

Sorry...me again...:-/

Last question for you I promise... ;^)

Can I modify H16 slightly whereby if the value in H16 is lower than H15 it
is highlighted RED (by using your formula this works as expected) but with
the proviso if H16 is below 75 then it turns RED anyway even if it is higher
than H15?

Currently with your CF formula in the scenario above, i.e. if H15 is 70 and
H16 is 74 then H16 is not flagged RED because it is higher than H15 but it
needs to be flagged RED as it is still lower than the 75 pass mark.

Does that make sense Biff?

I know what I mean but it is late and I am tired...;^)

Thanks & regards,
-=pp=-



"Biff" <[email protected]> wrote in message
news:%[email protected]...
> snip>>>>>>
> > One last question if I may...as I use H15 & H16 to input the scores of
> > what
> > I call Pre and Post Test results is there a way to highlight H16 if it
> > turns
> > out to be lower than the score in H15?
>
> For H16:
>
> Formula is: =AND(COUNT(H15,H16)=2,H16<H15)
>
> Using Count makes sure both cells are not empty and both cells contain
> numbers and it's shorter than:
>
> =AND(ISNUMBER(H15),ISNUMBER(H16),H16<H15)
> =AND(COUNT(H15,H16)=2,H16<H15)
>
> Biff

[Biff]

No problem!

Try this:

=AND(COUNT(H15,H16)=2,OR(H16<H15,H16<75))

Biff

"Pheasant PluckerŪ" <[email protected]> wrote in message
news:%[email protected]...
> Sorry...me again...:-/
>
> Last question for you I promise... ;^)
>
> Can I modify H16 slightly whereby if the value in H16 is lower than H15 it
> is highlighted RED (by using your formula this works as expected) but with
> the proviso if H16 is below 75 then it turns RED anyway even if it is
> higher
> than H15?
>
> Currently with your CF formula in the scenario above, i.e. if H15 is 70
> and
> H16 is 74 then H16 is not flagged RED because it is higher than H15 but it
> needs to be flagged RED as it is still lower than the 75 pass mark.
>
> Does that make sense Biff?
>
> I know what I mean but it is late and I am tired...;^)
>
> Thanks & regards,
> -=pp=-
>
>
>
> "Biff" <[email protected]> wrote in message
> news:%[email protected]...
>> snip>>>>>>
>> > One last question if I may...as I use H15 & H16 to input the scores of
>> > what
>> > I call Pre and Post Test results is there a way to highlight H16 if it
>> > turns
>> > out to be lower than the score in H15?
>>
>> For H16:
>>
>> Formula is: =AND(COUNT(H15,H16)=2,H16<H15)
>>
>> Using Count makes sure both cells are not empty and both cells contain
>> numbers and it's shorter than:
>>
>> =AND(ISNUMBER(H15),ISNUMBER(H16),H16<H15)
>> =AND(COUNT(H15,H16)=2,H16<H15)
>>
>> Biff
>
>