Excel macro - comparing numbers and setting colour

  haven't a clue 10:06 07 Feb 2005
Locked

Hi folks,

I need a macro that will compare two sets of numbers and if they vary by more than x% set the attributes of one of the numbers to make it red

Much appreciation in advance.

  Simsy 12:24 07 Feb 2005

but something along these lines;


I have 3 named cells;

"First" where the first number is entered

"Second" where the second numbr is enterd

"Variation" where the value "x" is entered



then enter the following into a module. (I stress I've not thought this through fully... the arithmetic may need a little tweaking!)


Sub CompareAndChangeColour()

On Error Resume Next

Dim First As Double
Dim Second As Double
Dim Ratio As Double
Dim Threshold As Double


First = Range("One").Value
Second = Range("Two").Value

Threshold = Range("Variation").Value

Ratio = First / Second

If Ratio >= 1 + (Threshold / 100) Or Ratio <= 1 - (Threshold / 100) Then

Range("B2").Font.ColorIndex = 3

Else

Range("B2").Font.ColorIndex = 5

End If

End Sub



If the comparison is outside the limits the number in "Second" will go red, otherwise it will go blue.


I hope this helps.

Regards,

Simsy

  Simsy 12:28 07 Feb 2005

site formatting,

Seperate lines would be better!!



Sub CompareAndChangeColour()

On Error Resume Next



Dim First As Double

Dim Second As Double

Dim Ratio As Double

Dim Threshold As Double



First = Range("One").Value

Second = Range("Two").Value

Threshold = Range("Variation").Value



Ratio = First / Second


If Ratio >= 1 + (Threshold / 100) Or Ratio <= 1 - (Threshold / 100) Then

Range("B2").Font.ColorIndex = 3

Else

Range("B2").Font.ColorIndex = 5

End If



End Sub


Hope this reads better,

Regards,

Simsy

  pc moron 13:13 07 Feb 2005

Can it not be done by conditional formatting?

I have a number in A1

The number I want to compare to the number in A1 is in B1.

Both numbers are positive.

The percentage is in cell E1 as a decimal (20% is 0.2).

I applied a conditional format to cell B1 that turns the text red if the number I type in this cell differs (bigger or smaller) by more than x% from the value in A1.

I had to enter three conditions to check for greater than, less than, or equal to:

=ABS(B1-A1)>A1*E1 turn text red.
=ABS(B1-A1)<A1*E1 turn text black
=ABS(B1-A1)=A1*E1 turn text black

  haven't a clue 14:17 07 Feb 2005

Simsy and pc moron, thanks to both of you. I think I will be able to use your advice.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?