Excel macro - comparing numbers and setting colour

  haven't a clue 10:06 07 Feb 05
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 05

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 05

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 05

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 05

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…