Using the answer from a formula (1 Viewer)

P

philipwaldram

New member

Local time

Today, 13:25

Joined

Aug 26, 2008

Messages

6

• Aug 27, 2008

• #1

Hi
I'm trying to use a piece of code to change the colour of a cell depending on the answer from that cells formula.
E.g. =a1+b1+c1 in cell d1
If the answer to the formula in d1 is 1 the cell colour needs to be red, if the answer is 2 the colour needs to be yellow and any other numbers green.

If Range("d1") = 1 Then
Selection.Interior.ColorIndex = 3
ElseIf Range("d1 = 2 Then
Selection.Interior.ColorIndex = 6
Else
Selection.Font.ColorIndex = 4
Selection.Interior.ColorIndex = 4
End If

But this doesn't work with the calculation. It only works when the number is typed in the cell.
Is is something like:
Range("d1").value

Thanks
Phil

 

ajetrumpet

Banned

Local time

Today, 15:25

Joined

Jun 22, 2007

Messages

5,638

• Aug 28, 2008

• #2

you don't need code to do this. The conditional formatting wizard will accomplish it. Click on the cell that contains the formula, and go to format > conditional formatting. Put as many conditions as you want in there.

 

S

shades

Registered User.

Local time

Today, 15:25

Joined

Mar 25, 2002

Messages

516

• Aug 28, 2008

• #3

Well, up to three conditions.

Anything more and then you need code.

 

P

philipwaldram

New member

Local time

Today, 13:25

Joined

Aug 26, 2008

Messages

6

• Aug 28, 2008

• #4

Thanks, thats made it a lot easier

 

P

philipwaldram

New member

Local time

Today, 13:25

Joined

Aug 26, 2008

Messages

6

• Aug 28, 2008

• #5

I've now come across another problem and need to use the value created by a formula in the code. How would I refer to this value?
Thanks Phil

 

V

Vassago

Former Staff Turned AWF Retiree

Local time

Today, 16:25

Joined

Dec 26, 2002

Messages

4,751

• Aug 29, 2008

• #6

philipwaldram said:

I've now come across another problem and need to use the value created by a formula in the code. How would I refer to this value?
Thanks Phil

Click to expand...

Conditional formatting should already be using values of the formula's to determine the format of the cell. What do you have for selected and typed for your conditional formats?

 

ajetrumpet

Banned

Local time

Today, 15:25

Joined

Jun 22, 2007

Messages

5,638

• Aug 29, 2008

• #7

philipwaldram said:

I've now come across another problem and need to use the value created by a formula in the code. How would I refer to this value?
Thanks Phil

Click to expand...

You should reference the cell. The value is automatically captured. Something like:

Code:Copy to clipboard

range("cell number")

should work fine.