Conditional Formatting Help

elew · #1 07-09-2005, 08:26 AM

I am trying to use conditional formatting to highlight cells in a column that are less than 150% of a value in another cell. Thanks

#2 07-09-2005, 10:05 AM

something like
formula is
=f4<1.5*e4

--
Don Guillett
SalesAid Software
donaldb@281.com
"elew" <elew.1rwlea_1120914301.1774@excelforum-nospam.com> wrote in message
news:elew.1rwlea_1120914301.1774@excelforum-nospam.com...
>
> I am trying to use conditional formatting to highlight cells in a column
> that are less than 150% of a value in another cell. Thanks
>
>
> --
> elew
> ------------------------------------------------------------------------
> elew's Profile:
http://www.excelforum.com/member.php...o&userid=25059
> View this thread: http://www.excelforum.com/showthread...hreadid=385830
>

elew · #3 07-09-2005, 11:04 AM

This does ot seem to do it. I have tried this in both Formula and Cell Value

Domenic · #4 07-09-2005, 11:37 AM

Quote:

Originally Posted by elew

I am trying to use conditional formatting to highlight cells in a column that are less than 150% of a value in another cell. Thanks

Assuming that B2:B100 contains your data, and that D2 contains the value to be compare to, try the following...

1) Select B2:B100

2) Format > Conditional Formatting > Formula Is

3) Enter the following formula:

=(B2<>"")*(B2<$D$2*1.5)

4) Choose your formatting

5) Click Ok

Hope this helps!

#5 07-09-2005, 12:05 PM

Probably a matter of not choosing the cells in the formula relative
to the active cell and your selection. Read (especially red lettering in)
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Then try again. You will have to adjust the formula Don supplied to meet
your needs. Also you realize that your response is vague. So if you can
describe what it does instead of working you'd be ahead of the game.
Of course that is not always possible.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"elew" <elew.1rwqyc_1120921502.5224@excelforum-nospam.com> wrote in message
news:elew.1rwqyc_1120921502.5224@excelforum-nospam.com...
>
> This does ot seem to do it. I have tried this in both Formula and Cell
> Value
>
>
> --
> elew
> ------------------------------------------------------------------------
> elew's Profile: http://www.excelforum.com/member.php...o&userid=25059
> View this thread: http://www.excelforum.com/showthread...hreadid=385830
>

#6 07-09-2005, 01:05 PM

To add to Domenic's reply, it's important that the active (white) cell of
the B2:B100 selection be B2 to use his formula. It probably would be, but
it's possible it not be. I thought it's worth a mention.

--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Domenic" <Domenic.1rwtqf_1120925104.4034@excelforum-nospam.com> wrote in
message news:Domenic.1rwtqf_1120925104.4034@excelforum-nospam.com...
>
> elew Wrote:
>> I am trying to use conditional formatting to highlight cells in a column
>> that are less than 150% of a value in another cell. Thanks
>
> Assuming that B2:B100 contains your data, and that D2 contains the
> value to be compare to, try the following...
>
> 1) Select B2:B100
>
> 2) Format > Conditional Formatting > Formula Is
>
> 3) Enter the following formula:
>
> =(B2<>"")*(B2<$D$2*1.5)
>
> 4) Choose your formatting
>
> 5) Click Ok
>
> Hope this helps!
>
>
> --
> Domenic
> ------------------------------------------------------------------------
> Domenic's Profile:
> http://www.excelforum.com/member.php...o&userid=10785
> View this thread: http://www.excelforum.com/showthread...hreadid=385830
>

#7 07-09-2005, 02:05 PM

In article <#I1WJUKhFHA.3316@TK2MSFTNGP14.phx.gbl>,
"Earl Kiosterud" <nothanks@nospam.com> wrote:

> To add to Domenic's reply, it's important that the active (white) cell of
> the B2:B100 selection be B2 to use his formula. It probably would be, but
> it's possible it not be. I thought it's worth a mention.

Thanks Earl! Good point! I assumed that B2 would be selected first,
but I suppose there may be those who would select B100 first. I'll have
to keep that in mind for the future.

#8 07-09-2005, 04:05 PM

Domenic,

Yeah. I often find it handy to start at the bottom, and drag up. That way,
I don't have to worry about going too much farther than I wanted to, since
it stops when I crash into the top. Lazy people have lots of tricks. Then
I can back down to the top cell of my desired selection before I let go of
Mr. Mouse. In this case, B2 wouldn't be active, though one press of Enter
fixes that.

--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-AA1E20.13203909072005@msnews.microsoft.com...
> In article <#I1WJUKhFHA.3316@TK2MSFTNGP14.phx.gbl>,
> "Earl Kiosterud" <nothanks@nospam.com> wrote:
>
>> To add to Domenic's reply, it's important that the active (white) cell of
>> the B2:B100 selection be B2 to use his formula. It probably would be,
>> but
>> it's possible it not be. I thought it's worth a mention.
>
> Thanks Earl! Good point! I assumed that B2 would be selected first,
> but I suppose there may be those who would select B100 first. I'll have
> to keep that in mind for the future.

#9 07-09-2005, 05:05 PM

lazy people who know where (think they know where) they are going use the name box,
and have toolbar buttons to get to top row and last row in a column
http://www.mvps.org/dmcritchie/excel/toolbars.htm

However for Conditional Formatting you might as well select the entire column
and if you want to start in B2 you can use arrow key to have that as the activecell.
Or you can select B2 and then Ctrl+Spacebar (c for column = ctrl)
If you are just testing numbers the text cells won't show up with True anyway.

If you have Excel 2003 (and I don't) you have to learn to use Ctrl+Shift+SpaceBar
to select all cells instead of using Ctrl+A. So that you select all rows (Shift+SpaceBar)
and all columns (Ctrl+SpaceBar). This is not the same as the gray button to left of the
column letters which will change the active cell.

The important page for laziness is, never mind it's my entire site, well fillhand.htm,
and shortx2k.htm and then all the other pages.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Earl Kiosterud" <nothanks@nospam.com> wrote in message news:%23tsrt8LhFHA.1460@tk2msftngp13.phx.gbl...
> Domenic,
>
> Yeah. I often find it handy to start at the bottom, and drag up. That way,
> I don't have to worry about going too much farther than I wanted to, since
> it stops when I crash into the top. Lazy people have lots of tricks. Then
> I can back down to the top cell of my desired selection before I let go of
> Mr. Mouse. In this case, B2 wouldn't be active, though one press of Enter
> fixes that.
>
> --
> Earl Kiosterud
> www.smokeylake.com/
> -------------------------------------------
>
> "Domenic" <domenic22@sympatico.ca> wrote in message
> news:domenic22-AA1E20.13203909072005@msnews.microsoft.com...
> > In article <#I1WJUKhFHA.3316@TK2MSFTNGP14.phx.gbl>,
> > "Earl Kiosterud" <nothanks@nospam.com> wrote:
> >
> >> To add to Domenic's reply, it's important that the active (white) cell of
> >> the B2:B100 selection be B2 to use his formula. It probably would be,
> >> but
> >> it's possible it not be. I thought it's worth a mention.
> >
> > Thanks Earl! Good point! I assumed that B2 would be selected first,
> > but I suppose there may be those who would select B100 first. I'll have
> > to keep that in mind for the future.

>
>

Welcome to the Excel Forum

Forum Statistics