Changing cell color based on mathematical outcome
 

I have been tasked with setting up a QA program for large amounts of
data. I have a lot of experience with Excel but none with VBA and I
know that conditional formatting cannot be done with a formula.

Simple example:

Cell A1 = 100
Cell A2 = 180

Wanted outcome:

IF(ABS(A1-A2)<100,"MAKE THE CELL RED","MAKE THE CELL GREEN")
IF(ABS(A2-A3)<100...ETC.

I need to loop this situation through the entire spreadsheet, which
over the course of time will have a variable amount of rows (could be
upwards of 50,000).

Honestly, I don't even know where to start to write the first line of
code. I played around with some commands, but nothing really made
sense to me.

Thanks for any help you may provide!

Douglas

Changing cell color based on mathematical outcome
 

in the conditional formatting dialog, change the first dropdown from Cell
Value is to Formula is, then you can put in your formula.

So you can use a formula with conditional formatting

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
I have been tasked with setting up a QA program for large amounts of
data. I have a lot of experience with Excel but none with VBA and I
know that conditional formatting cannot be done with a formula.

Simple example:

Cell A1 = 100
Cell A2 = 180

Wanted outcome:

IF(ABS(A1-A2)<100,"MAKE THE CELL RED","MAKE THE CELL GREEN")
IF(ABS(A2-A3)<100...ETC.

I need to loop this situation through the entire spreadsheet, which
over the course of time will have a variable amount of rows (could be
upwards of 50,000).

Honestly, I don't even know where to start to write the first line of
code. I played around with some commands, but nothing really made
sense to me.

Thanks for any help you may provide!

Douglas

Changing cell color based on mathematical outcome
 

CF can be done with a formula.

Got to CF
Change condition 1 to Formula Is
Add a formula of ABS(A1-A2)<100
Set that to red
Add another condition
Use a formula of ABS(A1-A2)=100
set to green
OK


--
HTH

Bob Phillips

wrote in message
oups.com...
I have been tasked with setting up a QA program for large amounts of
data. I have a lot of experience with Excel but none with VBA and I
know that conditional formatting cannot be done with a formula.

Simple example:

Cell A1 = 100
Cell A2 = 180

Wanted outcome:

IF(ABS(A1-A2)<100,"MAKE THE CELL RED","MAKE THE CELL GREEN")
IF(ABS(A2-A3)<100...ETC.

I need to loop this situation through the entire spreadsheet, which
over the course of time will have a variable amount of rows (could be
upwards of 50,000).

Honestly, I don't even know where to start to write the first line of
code. I played around with some commands, but nothing really made
sense to me.

Thanks for any help you may provide!

Douglas

Changing cell color based on mathematical outcome
 

Thanks guys and I appreciate the replies... but it's very hard
sometimes to clearly state what the problem might be...

What was described above turns the cell with the formula a specific
color... I want the formula (or VBA) to turn the cell with the actual
numbers (i.e. A1, A2) a color based on the critieria.

Changing cell color based on mathematical outcome
 

I think that that is what Tom and I were assuming in our replies. The
formulas and the result values are I presume in the same cell?

--
HTH

Bob Phillips

wrote in message
oups.com...
Thanks guys and I appreciate the replies... but it's very hard
sometimes to clearly state what the problem might be...

What was described above turns the cell with the formula a specific
color... I want the formula (or VBA) to turn the cell with the actual
numbers (i.e. A1, A2) a color based on the critieria.

Changing cell color based on mathematical outcome
 

unfortunately no...

I have ascii data that gets parsed and brought into Excel. Now I need
to analyze the data, so if I write a formula, it can't be in the same
cell as the results and I can't have extra cells... that's why i was
thinking macro... so something could run in the background and color
the cells with the raw data without creating cells with formulas.

Changing cell color based on mathematical outcome
 

You just reference the cell with the data in the formula. For instance in B1
you could use

=A1=10

and it returns a TRUE or FALSE that CF works on.


--
HTH

Bob Phillips

wrote in message
oups.com...
unfortunately no...

I have ascii data that gets parsed and brought into Excel. Now I need
to analyze the data, so if I write a formula, it can't be in the same
cell as the results and I can't have extra cells... that's why i was
thinking macro... so something could run in the background and color
the cells with the raw data without creating cells with formulas.

Changing cell color based on mathematical outcome
 

and (for emphasis) that formula is in the condition formatting applied to
the cell (not in the cell itself - so there is no conflict with the data)
using the methods we described.

If you want to send a sample workbook with you data and an explanation of
what you want to see to



I will see if I can give you some examples.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
You just reference the cell with the data in the formula. For instance in
B1
you could use

=A1=10

and it returns a TRUE or FALSE that CF works on.


--
HTH

Bob Phillips

wrote in message
oups.com...
unfortunately no...

I have ascii data that gets parsed and brought into Excel. Now I need
to analyze the data, so if I write a formula, it can't be in the same
cell as the results and I can't have extra cells... that's why i was
thinking macro... so something could run in the background and color
the cells with the raw data without creating cells with formulas.