ExcelBanter - Conditional Formatting Help

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Conditional Formatting Help (https://www.excelbanter.com/excel-discussion-misc-queries/34564-conditional-formatting-help.html)

Conditional Formatting Help

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

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

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" wrote in message
...

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

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

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" wrote in
message ...

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

In article ,
"Earl Kiosterud" 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. :)

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" wrote in message
...
In article ,
"Earl Kiosterud" 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. :)

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" wrote in message ...
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" wrote in message
...
In article ,
"Earl Kiosterud" 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. :)