Need formula to shade cell if < or > half of another cell

[brians34]

To clarify title. Need to be able to shade a cell if the input number is , < half the number in another cell or > 1.5 that same cell. Can be same color shade. Shade doesn't matter.


Example: Cell A1 is 50, I need to set B1 to shade if < 25 or > 75
Cell A2 is 25, B2 to shade if < 12.5 or > 37.5

Etc.

[etaf]

OR(b1<A1/2, b1>A1*1.5)

if you put

OR(b1<A1/2, b1>A1*1.5)
in conditional format
and choose colour - that will do what I think you need

see attached example

thought I would test a script for conditional formatting - as i like the detail in jeffreybrown reply - hope you dont mind the copy

for 2007 or 2010 excel version

Conditional Formatting

Highlight applicable range >>

B1

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=OR(B1<A1/2,B1>A1*1.5)

Format… [Number, Font, Border, Fill]
OK >> OK

[jeffreybrown]

Try...

Conditional Formatting

• Highlight applicable range >> B1
• Home Tab >> Styles >> Conditional Formatting
• New Rule >> Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =OR(B1<A1/2,B1>A1*1.5)
• Format… [Number, Font, Border, Fill]
• OK >> OK

[Craig K.]

To build on what the other guys told you. If you want the cells to remain unformatted if blank, add another conditional format that is "Format only cells that contain" and choose "blanks". Make this the first (top) condition and check the box to the right to "Stop If True".

[brians34]

Thanks to all three of you.

Great help.