Changing cell color help.

[tarponwilly]

I am running a live (real-time quotes) spreadsheet. What I am trying t
do is put a sell number in the L column, and when the number comes u
in my B column cell, I would like my L column cell to turn red. How ca
I do this? Specifs please if you know, I am a rookie in Excel.
Thanks

 

[tarponwilly]

I should give an example:

If I have the number 45.20(live and changing) in B2 and
45.45 in L2 (specific number), when B2 hits 45.45, I want L2 to turn
red.

Thanks for any help.

 

[Jerry W. Lewis]

You want to use Format|Conditional Formatting on the cell in column L

It is difficult to be specific, because you were not specific. What
cell in column L? What cell or cells in column B? Do you want the text
or the background to turn red?

If a specific cell in column B, change the second conditional formatting
field from "between" to "equal to", click in the third conditional
formatting field and select the specific cell in column B.

If a range of cells in column B, change the first conditional formatting
field from "Cell value is" to "Formula is" and put the formula
=ISNUMBER(MATCH(<L-cell>,<B-range>,0))
in the second conditional formatting field, where you replace the <>
descriptions with the appropriate cell references.

Jerry

 

[Jerry W. Lewis]

Forgot to mention, once you define the condition, you need to press the
Format button and define the formatting changes that you want to occur
when a match occurs.

Jerry

 

[tarponwilly]

Thanks for your help so far.

I need a little more help.

Here is the formula I used:
Formula is=ISNUMBER(MATCH(L2,B2,0))

Ok. 1st question: When Both cells match, it turns red. Great. If B
goes above L2 it DOES NOT stay red. What do I need to change in th
formula to keep the cell red when it exceeds the number.
OR ..Once it hits the number, the cell will stay highlighted EVEN if i
goes back below the other number in cell L2?


2nd question: When I get the formula working, what do I need to add t
make it do the same for Cells B2 to B20, and L2 to L20

 

[Jerry W. Lewis]

To check a single specific cell, it will be easier if you change the
first field back to "Cell Value Is" from "Formula Is". Chance the
second field to "less than or equal to" and put B2 in the third field

Memory is beyond the scope of conditional formatting. You will have to
write an event triggered macro.

Tools|Macro Editor and double click on the appropriate worksheet in the
Project window. Add the following code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B2:B20"))
If Not (isect Is Nothing) Then
If Target.Value >= [L2].Value Then [L2].Font.ColorIndex = 3
End If
End Sub

Jerry