Cell comparison to change color on a third cell.
 

I have a "template" of sorts. Basicly it has several columns. One
column has the nominal value. The next has a percentage value and the
third is where the data taken is inserted.

What I would like to do is Take the first value. Find the upper and
lower limits per the second value. Compare those limits to the third
value and if it is out of the range of the limits change the color of
the cell or circle the cell.

I'm not all that familiar with Excel programming and any help would be
appreciated.

Thanks

Dannic
Http://www.orpgs.com

Cell comparison to change color on a third cell.
 

Dannic

assuming the value is in A2, the percentage (plus or minus) is in B2 and the
value to be checked is in cell C2 ...

Set the Conditional Formatting to "Formula is"
=OR($C$2<$A$2*(1-$B$2),$C$2$A$2*(1+$B$2))
and pick a format, for example red font.

So, with 100 in Cell A2, 10% in Cell B2, values below 90 will be shown in a
red font, as will values above 110

Regards

Trevor


"Dannic" wrote in message
om...
I have a "template" of sorts. Basicly it has several columns. One
column has the nominal value. The next has a percentage value and the
third is where the data taken is inserted.

What I would like to do is Take the first value. Find the upper and
lower limits per the second value. Compare those limits to the third
value and if it is out of the range of the limits change the color of
the cell or circle the cell.

I'm not all that familiar with Excel programming and any help would be
appreciated.

Thanks

Dannic
Http://www.orpgs.com

Cell comparison to change color on a third cell.
 

Dannic,

No programming needed.

With the nominal value in A1, the percentage (entered as a percentage)
in B1, use Format | Conditional formatting... on cell C1, selecting
"Formula Is" with the formula

=OR(C1A1*(1+B1),C1<A1*(1-B1))

Set your formatting pattern to the color of your choice

--
HTH,
Bernie


"Dannic" wrote in message
om...
I have a "template" of sorts. Basicly it has several columns. One
column has the nominal value. The next has a percentage value and
the
third is where the data taken is inserted.

What I would like to do is Take the first value. Find the upper and
lower limits per the second value. Compare those limits to the
third
value and if it is out of the range of the limits change the color
of
the cell or circle the cell.

I'm not all that familiar with Excel programming and any help would
be
appreciated.

Thanks

Dannic
Http://www.orpgs.com

Cell comparison to change color on a third cell.
 

Hi Bernie/Dannic - could I ask a follow on question?

Can you get a cell color to change if the value in the
cell is changed? For example, if the value in cell A1 is
10 and a user changes it to any other number, could the
fill color be changed automatically (say from yellow to
blue)? I only want this to happen on selected cells
(maybe a dozen or so).

I'm currently using code to do this but it seems like your
way might be a lot simpler!

Thanks! Kathryn

-----Original Message-----
Dannic,

No programming needed.

With the nominal value in A1, the percentage (entered as
a percentage)
in B1, use Format | Conditional formatting... on cell
C1, selecting
"Formula Is" with the formula

=OR(C1A1*(1+B1),C1<A1*(1-B1))

Set your formatting pattern to the color of your choice

--
HTH,
Bernie


"Dannic" wrote in message
. com...
I have a "template" of sorts. Basicly it has several
columns. One
column has the nominal value. The next has a
percentage value and
the
third is where the data taken is inserted.

What I would like to do is Take the first value. Find
the upper and
lower limits per the second value. Compare those
limits to the
third
value and if it is out of the range of the limits
change the color
of
the cell or circle the cell.

I'm not all that familiar with Excel programming and
any help would
be
appreciated.

Thanks

Dannic
Http://www.orpgs.com


.

Cell comparison to change color on a third cell.
 

While this does work it makes it difficult when there are 200 lines of
this to do. Is there a programmatic way of doing this to make it
easier to do? I would hate to do this cell by cell.

I also need to remove some text from those cells and take just the
Number values. For example:

1 2 3

190mv +/-2.5% 189.5

The conditional works but it is a real pain when it comes to 100 or so
lines that I would have to do for each template. I have about 300
templates to apply this to so it would take me a very long time to do.
And they are not all the same. I need a vb script to accomplish what
I am looking for. Least till someone can show me another way of doing
it without a script.

Dannic
Http://www.orpgs.com

"Bernie Deitrick" wrote in message ...
Dannic,

No programming needed.

With the nominal value in A1, the percentage (entered as a percentage)
in B1, use Format | Conditional formatting... on cell C1, selecting
"Formula Is" with the formula

=OR(C1A1*(1+B1),C1<A1*(1-B1))

Set your formatting pattern to the color of your choice

--
HTH,
Bernie


"Dannic" wrote in message
om...
I have a "template" of sorts. Basicly it has several columns. One
column has the nominal value. The next has a percentage value and
the
third is where the data taken is inserted.

What I would like to do is Take the first value. Find the upper and
lower limits per the second value. Compare those limits to the
third
value and if it is out of the range of the limits change the color
of
the cell or circle the cell.

I'm not all that familiar with Excel programming and any help would
be
appreciated.

Thanks

Dannic
Http://www.orpgs.com

Cell comparison to change color on a third cell.
 

Kathryn,

Conditional formatting cannot remember previous values, so cannot be
used solely to highlight changes. You need to use the change event
code to change the color of the cell or, at least, to store the old
value in another cell for comparison purposes when applying
conditional formatting.

HTH,
Bernie


"Kathryn" wrote in message
...
Hi Bernie/Dannic - could I ask a follow on question?

Can you get a cell color to change if the value in the
cell is changed? For example, if the value in cell A1 is
10 and a user changes it to any other number, could the
fill color be changed automatically (say from yellow to
blue)? I only want this to happen on selected cells
(maybe a dozen or so).

I'm currently using code to do this but it seems like your
way might be a lot simpler!

Thanks! Kathryn

-----Original Message-----
Dannic,

No programming needed.

With the nominal value in A1, the percentage (entered as
a percentage)
in B1, use Format | Conditional formatting... on cell
C1, selecting
"Formula Is" with the formula

=OR(C1A1*(1+B1),C1<A1*(1-B1))

Set your formatting pattern to the color of your choice

--
HTH,
Bernie


"Dannic" wrote in message
. com...
I have a "template" of sorts. Basicly it has several
columns. One
column has the nominal value. The next has a
percentage value and
the
third is where the data taken is inserted.

What I would like to do is Take the first value. Find
the upper and
lower limits per the second value. Compare those
limits to the
third
value and if it is out of the range of the limits
change the color
of
the cell or circle the cell.

I'm not all that familiar with Excel programming and
any help would
be
appreciated.

Thanks

Dannic
Http://www.orpgs.com


.

Cell comparison to change color on a third cell.
 

Dannic,

Depending on your layout, the same conditional formatting can be
applied to all the cells at one, even hundreds of rows. But applying
the conditional formatting to hundreds of files can be tedious.
Perhaps you could use application-level events to apply the formatting
to each opened workbook. See

http://www.cpearson.com/excel/AppEvent.htm

for how to program application-level events.

HTH,
Bernie


"Dannic" wrote in message
om...
While this does work it makes it difficult when there are 200 lines
of
this to do. Is there a programmatic way of doing this to make it
easier to do? I would hate to do this cell by cell.

I also need to remove some text from those cells and take just the
Number values. For example:

1 2 3

190mv +/-2.5% 189.5

The conditional works but it is a real pain when it comes to 100 or
so
lines that I would have to do for each template. I have about 300
templates to apply this to so it would take me a very long time to
do.
And they are not all the same. I need a vb script to accomplish
what
I am looking for. Least till someone can show me another way of
doing
it without a script.

Dannic
Http://www.orpgs.com

"Bernie Deitrick" wrote in message
...
Dannic,

No programming needed.

With the nominal value in A1, the percentage (entered as a
percentage)
in B1, use Format | Conditional formatting... on cell C1,
selecting
"Formula Is" with the formula

=OR(C1A1*(1+B1),C1<A1*(1-B1))

Set your formatting pattern to the color of your choice

--
HTH,
Bernie


"Dannic" wrote in message
om...
I have a "template" of sorts. Basicly it has several columns.
One
column has the nominal value. The next has a percentage value
and
the
third is where the data taken is inserted.

What I would like to do is Take the first value. Find the upper
and
lower limits per the second value. Compare those limits to the
third
value and if it is out of the range of the limits change the
color
of
the cell or circle the cell.

I'm not all that familiar with Excel programming and any help
would
be
appreciated.

Thanks

Dannic
Http://www.orpgs.com

Cell comparison to change color on a third cell.
 

Dannic, you could try something like this. I used "i" to
limit the number of rows I want looked at. If you are
looking at a large number of rows, you could increase to
50, 100, etc.

Option Explicit
Sub Review_WSvalues()
Dim i As Integer

Range("c5").Select 'enter 1st cell of the column w/data
i = 1
While i < 10
If ActiveCell.Value 0 Then
If ActiveCell.Value (ActiveCell.Offset(0, -2).Value
- ActiveCell.Offset(0, -2).Value _
* ActiveCell.Offset(0, -1).Value) And
ActiveCell.Value < (ActiveCell.Offset(0, -2).Value _
+ ActiveCell.Offset(0, -2).Value *
ActiveCell.Offset(0, -1).Value) Then
Selection.Interior.ColorIndex = 37
End If
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Wend
End Sub
-----Original Message-----
I have a "template" of sorts. Basicly it has several
columns. One
column has the nominal value. The next has a percentage
value and the
third is where the data taken is inserted.

What I would like to do is Take the first value. Find
the upper and
lower limits per the second value. Compare those limits
to the third
value and if it is out of the range of the limits change
the color of
the cell or circle the cell.

I'm not all that familiar with Excel programming and any
help would be
appreciated.

Thanks

Dannic
Http://www.orpgs.com
.

Cell comparison to change color on a third cell.
 

Dannic, you could try something like this. I used "i" to
limit the number of rows I want looked at. If you are
looking at a large number of rows, you could increase to
50, 100, etc.

Option Explicit
Sub Review_WSvalues()
Dim i As Integer

Range("c5").Select 'enter 1st cell of the column w/data
i = 1
While i < 10
If ActiveCell.Value 0 Then
If ActiveCell.Value (ActiveCell.Offset(0, -2).Value
- ActiveCell.Offset(0, -2).Value _
* ActiveCell.Offset(0, -1).Value) And
ActiveCell.Value < (ActiveCell.Offset(0, -2).Value _
+ ActiveCell.Offset(0, -2).Value *
ActiveCell.Offset(0, -1).Value) Then
Selection.Interior.ColorIndex = 37
End If
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Wend
End Sub
-----Original Message-----
I have a "template" of sorts. Basicly it has several
columns. One
column has the nominal value. The next has a percentage
value and the
third is where the data taken is inserted.

What I would like to do is Take the first value. Find
the upper and
lower limits per the second value. Compare those limits
to the third
value and if it is out of the range of the limits change
the color of
the cell or circle the cell.

I'm not all that familiar with Excel programming and any
help would be
appreciated.

Thanks

Dannic
Http://www.orpgs.com
.

Cell comparison to change color on a third cell.
 

Ok... I think I can figure that part out. But where do I put it and
how do i get it to run? Does it run automatically? Or will I have to
push the run macro button each time I want to check the cells? Which
would be ok for me but this is for several people and I need to make
it "idiot" proof.

I tried to do a sub where it made the active cell bold but I
appearntly didn't know enough about what I was doing to make it work
like it was supposed to. So I need a lil more help in this area.

I really know ever little at this point about excel programming.

What I really want this to do is just change the color of the cell
when someone enters in a value that's outside the limits. They
shouldn't have to push any buttons or click on anything to get it to
work. I know I'm asking a lot but if someone would just point me in
the right direction I can figure it out. At the current point I don't
have enough information to understand yet :)

Thanks for the help so far btw.... it has been very informative and I
have already learned a lot.

Dannic
http://www.orpgs.com

"Kathryn" wrote in message ...
Dannic, you could try something like this. I used "i" to
limit the number of rows I want looked at. If you are
looking at a large number of rows, you could increase to
50, 100, etc.

Option Explicit
Sub Review_WSvalues()
Dim i As Integer

Range("c5").Select 'enter 1st cell of the column w/data
i = 1
While i < 10
If ActiveCell.Value 0 Then
If ActiveCell.Value (ActiveCell.Offset(0, -2).Value
- ActiveCell.Offset(0, -2).Value _
* ActiveCell.Offset(0, -1).Value) And
ActiveCell.Value < (ActiveCell.Offset(0, -2).Value _
+ ActiveCell.Offset(0, -2).Value *
ActiveCell.Offset(0, -1).Value) Then
Selection.Interior.ColorIndex = 37
End If
End If
ActiveCell.Offset(1, 0).Select
i = i + 1
Wend
End Sub
-----Original Message-----
I have a "template" of sorts. Basicly it has several
columns. One
column has the nominal value. The next has a percentage
value and the
third is where the data taken is inserted.

What I would like to do is Take the first value. Find
the upper and
lower limits per the second value. Compare those limits
to the third
value and if it is out of the range of the limits change
the color of
the cell or circle the cell.

I'm not all that familiar with Excel programming and any
help would be
appreciated.

Thanks

Dannic
Http://www.orpgs.com
.