Formula for Conditional Formatting

[justin.arnold2]

I've recently been asked to create some conditional formatting based
on a cell value. Basically what I am trying to do is have the cell
turn red if the value of a cell is outside of a range.

For example, if the value of cell F12 > 280 OR < 220 then I want the
cell to turn red. The standard conditional formatting has limitations
so I am left to figure out how to write a formula. Can someone assist
me with this?

Here is what I have so far but it does not seem to be working.

=IF(F12>280 OR <220) then?????

Thanks in advance for your help.

 

[Peter T]

Sub Test()

With ActiveSheet.Range("A1").FormatConditions
.Delete
.Add Type:=xlExpression, _
Formula1:="=OR($F$12<220,$F$12>280)"
.Item(1).Interior.ColorIndex = 3
End With

End Sub

Change "A1" to the cell address in which you want the FC

Regards,
Peter T

 

[John]

Hi Justin
The formula that Peter is using in is Macro can also be used in Conditional
Formatting.
Your choice a macro that will do the job for you or you type it in yourself.
Regards
John

 

[Peter T]

I should have thought to mention the macro only does what is typically done
manually.

Apart from the formula that can be directly copied from the example macro,
need to select "Formula Is" in the left dropdown in the CF dialog.

Regards
Peter T

 

[justin.arnold2]

I should have thought to mention the macro only does what is typically done
manually.

Apart from the formula that can be directly copied from the example macro,
need to select "Formula Is" in the left dropdown in the CF dialog.

Regards
Peter T






- Show quoted text -

Thanks to everyone for your help. One thing that I have noticed is
that Cell F12 is actually pulling it's value from a second sheet.

When I select cell F12 I get the following in the formula bar -
='Sheet2'!E8

Since cell F12 does not contain a real value, how can I write the
formula to look at 'Sheet2'!E8 and if it meets the previously
mentioned critera then turn cell F12 on Sheet 1 red?

 

[Peter T]

When I select cell F12 I get the following in the formula bar -
='Sheet2'!E8

Since cell F12 does not contain a real value, how can I write the
formula to look at 'Sheet2'!E8 and if it meets the previously
mentioned critera then turn cell F12 on Sheet 1 red

Even if F12 contains a formula that refers to a cell on another sheet it
also contains a value (as returned by the formula). Normally this should not
make any difference to the way to add the CF formula as suggested (although
in some scenarios the order of calculation may affect things).

In an FC formula you can't refer directly to a cell on another sheet, at
least not directly. The workaround is to incorporate the Indirect function.
Though in this case that shouldn't be necessary, simply refer to the formula
cell F12.

Regards,
Peter T

 

[justin.arnold2]

Even if F12 contains a formula that refers to a cell on another sheet it
also contains a value (as returned by the formula). Normally this should not
make any difference to the way to add the CF formula as suggested (although
in some scenarios the order of calculation may affect things).

In an FC formula you can't refer directly to a cell on another sheet, at
least not directly. The workaround is to incorporate the Indirect function.
Though in this case that shouldn't be necessary, simply refer to the formula
cell F12.

Regards,
Peter T

Thanks for clearing that up.

If I understand correctly, I need to highlight cell F12, click Format,
Conditional Formatting.

I then select Formula Is from the drop down box and enter
Formula1:="=OR($F$12<220,$F$12>280)"

I then choose the formatting options and this should work?

 

[Peter T]

If I understand correctly, I need to highlight cell F12, click
Format, Conditional Formatting.

If you want the CF to dispay in the same cell that contains the value
(formula) yes

I then select Formula Is from the drop down box and enter
Formula1:="=OR($F$12<220,$F$12>280)"

No! (well you could but not the best way for this)

Cell Value Is : Not between : 200 : And : 280

I then choose the formatting options and this should work?

Yes

Regards,
Peter T

 

[justin.arnold2]

If you want the CF to dispay in the same cell that contains the value
(formula) yes


No! (well you could but not the best way for this)

Cell Value Is : Not between : 200 : And : 280


Yes

Regards,
Peter T

Thanks Again. I went ahead and tried to use the Cell Value Is : Not
between: 220 and 280. The problem that I am seeing is that the value
of the cell is currently 275 and the cell still changes to red. If I
manually re-enter 275 into that same cell it changes back to black and
white. Any idea why?

 

[Peter T]

Thanks Again. I went ahead and tried to use the
Cell Value Is : Not between: 220 and 280.
The problem that I am seeing is that the value
of the cell is currently 275 and the cell still changes to red.

When you say "changes to red" to you mean "it continues to be red".

However if you mean what you say afraid I have no idea.

Regards,
Peter

 

[justin.arnold2]

When you say "changes to red" to you mean "it continues to be red".

However if you mean what you say afraid I have no idea.

Regards,
Peter

Until I apply the CF all cells are white with black lettering. If I
create a CF with the following criteria:

Cell Value Is : Not between: 220 and 280

AND the value in that cell is presently 275, then based in the CF,
nothing should change, Correct? If the value went above 280 or below
220 then the cell should change to a red background. Presently, this
is not what is happening. The cell changes to red even though the
value is between 220 and 280. I'm not sure why this is.

 

[Peter T]

When you say "changes to red" to you mean "it continues to be red".

However if you mean what you say afraid I have no idea.

Regards,
Peter

Until I apply the CF all cells are white with black lettering. If I
create a CF with the following criteria:

Cell Value Is : Not between: 220 and 280

AND the value in that cell is presently 275, then based in the CF,
nothing should change, Correct? If the value went above 280 or below
220 then the cell should change to a red background. Presently, this
is not what is happening. The cell changes to red even though the
value is between 220 and 280. I'm not sure why this is.

-----------------------------------------------------------

OK I follow. I can only assume this relates to what I mentioned earlier
about calculation order. For the moment I can't recreate but from memory
think that's what it is.

Try the following:
In a cell somewhere on the same sheet as your formula CF cell, say in A1

=OR(Sheet2!E8<200,Sheet2!E8>280)

for the FC in F8 on the same sheet as the above formula
Formula Is : =$A$1 ' change $A$1 to the formula with the above
formula
apply the format for the true condition

Regards,
Peter T

PS you might need to embrace those sheet names with pairs of ' apostrophes

 

[John]

Hi Justin
Previously you said:> The problem that I am seeing is that the value
of the cell is currently 275 and the cell still changes to red. If I
manually re-enter 275 into that same cell it changes back to black and
white. Any idea why?
Question: Are you importing data from the internet, if so
you may have non-breaking space CHAR(160) with your numbers.
Excel will treat it as text. You can check it by typing in any empty cell
with the function
=ISNUMBER(cell reference) result if its "False" its Text.
To remove all CHAR(160)
Highlight all the cells, then CTRL-H (Find & Replace)
Find what: Press Alt and type 0160 from your Numeric keypad.
Replace with: leave blank
Click: Replace All
HTH
John

If you want the CF to dispay in the same cell that contains the value
(formula) yes


No! (well you could but not the best way for this)

Cell Value Is : Not between : 200 : And : 280


Yes

Regards,
Peter T

Thanks Again. I went ahead and tried to use the Cell Value Is : Not
between: 220 and 280. The problem that I am seeing is that the value
of the cell is currently 275 and the cell still changes to red. If I
manually re-enter 275 into that same cell it changes back to black and
white. Any idea why?

 

[justin.arnold2]

Hi Justin
Previously you said:> The problem that I am seeing is that the value
of the cell is currently 275 and the cell still changes to red. If I
manually re-enter 275 into that same cell it changes back to black and
white. Any idea why?
Question: Are you importing data from the internet, if so
you may have non-breaking space CHAR(160) with your numbers.
Excel will treat it as text. You can check it by typing in any empty cell
with the function
=ISNUMBER(cell reference) result if its "False" its Text.
To remove all CHAR(160)
Highlight all the cells, then CTRL-H  (Find & Replace)
Find what:  Press Alt and type 0160 from your Numeric keypad.
Replace with:  leave blank
Click:  Replace All
HTH






Thanks Again. I went ahead and tried to use the Cell Value Is : Not
between: 220 and 280. The problem that I am seeing is that the value
of the cell is currently 275 and the cell still changes to red. If I
manually re-enter 275 into that same cell it changes back to black and
white. Any idea why?- Hide quoted text -

- Show quoted text -

Thanks. The values in the cell in which I am trying to use CF are
actually being brought into the sheet via a database query. Could this
be the problem? If so, will your proposed solution solve the problem?
Thanks for your help.

 

[John]

Hi Justin
Did you try to test anything, if you're scared of doing something wrong,
make a copy of your document and work with that.
I can't tell you if what I proposed will work, you need to try it, we don't
see your document. Do some test and comeback with your results.
Good Luck
John

Hi Justin
Previously you said:> The problem that I am seeing is that the value
of the cell is currently 275 and the cell still changes to red. If I
manually re-enter 275 into that same cell it changes back to black and
white. Any idea why?
Question: Are you importing data from the internet, if so
you may have non-breaking space CHAR(160) with your numbers.
Excel will treat it as text. You can check it by typing in any empty cell
with the function
=ISNUMBER(cell reference) result if its "False" its Text.
To remove all CHAR(160)
Highlight all the cells, then CTRL-H (Find & Replace)
Find what: Press Alt and type 0160 from your Numeric keypad.
Replace with: leave blank
Click: Replace All
HTH






Thanks Again. I went ahead and tried to use the Cell Value Is : Not
between: 220 and 280. The problem that I am seeing is that the value
of the cell is currently 275 and the cell still changes to red. If I
manually re-enter 275 into that same cell it changes back to black and
white. Any idea why?- Hide quoted text -

- Show quoted text -

Thanks. The values in the cell in which I am trying to use CF are
actually being brought into the sheet via a database query. Could this
be the problem? If so, will your proposed solution solve the problem?
Thanks for your help.

 

[justin.arnold2]

Hi Justin
Did you try to test anything, if you're scared of doing something wrong,
make a copy of your document and work with that.
I can't tell you if what I proposed will work, you need to try it, we don't
see your document. Do some test and comeback with your results.
Good Luck






Thanks. The values in the cell in which I am trying to use CF are
actually being brought into the sheet via a database query. Could this
be the problem? If so, will your proposed solution solve the problem?
Thanks for your help.- Hide quoted text -

- Show quoted text -

I finally found a solution. I added *1 to the end of each cell that I
wanted to apply CF to. As a result, Excel formatted these cell as a
true number. This allowed me to apply the conditional formatting that
I was looking for. All seems to be working well. Thanks to everyone
for your assistance.

 

[John]

Glad you got it working.
You're welcome
John

Hi Justin
Did you try to test anything, if you're scared of doing something wrong,
make a copy of your document and work with that.
I can't tell you if what I proposed will work, you need to try it, we
don't
see your document. Do some test and comeback with your results.
Good Luck






Thanks. The values in the cell in which I am trying to use CF are
actually being brought into the sheet via a database query. Could this
be the problem? If so, will your proposed solution solve the problem?
Thanks for your help.- Hide quoted text -

- Show quoted text -

I finally found a solution. I added *1 to the end of each cell that I
wanted to apply CF to. As a result, Excel formatted these cell as a
true number. This allowed me to apply the conditional formatting that
I was looking for. All seems to be working well. Thanks to everyone
for your assistance.