E
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
that are less than 150% of a value in another cell. Thanks
D
Don Guillett
something like
formula is
=f4<1.5*e4
formula is
=f4<1.5*e4
E
elew
This does ot seem to do it. I have tried this in both Formula and Cell
Value
Value
D
David McRitchie
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.
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.
D
Domenic
elew said:
Assuming that B2:B100 contains your data, and that D2 contains th
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
E
Earl Kiosterud
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.
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.
D
Domenic
Earl Kiosterud said:
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.
E
Earl Kiosterud
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.
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.
D
David McRitchie
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.
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.