# Compare Values Above, Equal and Below

• 7th January 2009, 01:39 PM
TwyloII
Compare Values Above, Equal and Below
Hi

OK I'm pretty good with some parts of excel but not great with macros or when it comes to comparing the difference between non numeric values, what I would like to do is give a cell a colour based on a comparison to a target level (above target ,on target, below target, way below target, beyond help!) the levels being

7a,7b,7c,6a,6b,6c,5a,5b,5c,4a,4b,4c,3a,3b,3c,2a,2b ,2c

starting at 7a and decreasing 1 increment all the way to 2c

say a target score was 7c,

if above, anything more than their target score, the conditional format would go blue
if on target (equal to whatever their target) the conditional format would go green
if more than 2 increments below their target, cell changes to amber
if more than 3 increments below their target, red
more than 6 points below their target, brown

I hope this makes sense!

AJ
• 7th January 2009, 02:41 PM
SimpleSi
Convert alpahnumeric to pure numeric and then try conditional formatting after that

e.g

2a

Convert a to 1 (the old asc-64 trick might still work but who knows in todays unicode world :) ) b to 2, c to 3.

do y=4-x so a becomes 3, b becomes 2, c becomes 1

multiply the 1st digit of orig 2a by 3 so you get 6 (or 9 if level 3 etc)

Add y plus above to get a linear numeric value
e.g
2a becomes (2x3)+1 =7
2c becomes (2x3)+3=9
3a becomes (3x3)+1=10

etc

And then all you have to do is the conidtional formating :)

(Don't know how to do that - it wasn't really around when I was a Multiplan guru (pre-cursor to Excel :) ) )

regards

Simon
• 7th January 2009, 02:53 PM
tmcd35
I was working on this early last term before I left my last job. Unfortunately I no longer have copies of the finished article (sorry). Basically the solution I used was to write a macro that copied the cell contents into a string, pull the string apart in to two variables (the integer and the a/b/c marker) and then use some if statements to compare the integer with the value in the cell next to it and checking the marker before assigning a colour to the cell.

Here is a copy of the last macro I have (if it helps).

Code:

```Sub BgColorInt()       Dim currRow As Integer     Dim currCol As Integer     Dim testVar As String     Dim BgColor As Integer     currRow = 2     currCol = 3         Do Until Cells(currRow, currCol).Value = ""         testVar = Cells(currRow, currCol).Value         testVar = Left(testVar, 1)                 If testVar < Cells(currRow, 2).Value Then             BgColor = 4         End If         If testVar = Cells(currRow, 2).Value Then             BgColor = 6         End If         If testVar > Cells(currRow, 2).Value Then             BgColor = 3         End If         If testVar = "a" Then             BgColor = 4         End If         With Cells(currRow, currCol).Interior             .ColorIndex = BgColor             .Pattern = xlSolid         End With                 currRow = currRow + 1             Loop     End Sub```
• 7th January 2009, 02:56 PM
CESIL
Conditional formatting will do this without any conversion of the data

ooops...I didn't read the original question properly...it's so cold in here today that my brain is seizing up!
• 7th January 2009, 06:08 PM
elsiegee40
Quote:

Originally Posted by SimpleSi
Convert alpahnumeric to pure numeric and then try conditional formatting after that

e.g

2a

Convert a to 1 (the old asc-64 trick might still work but who knows in todays unicode world :) ) b to 2, c to 3.

do y=4-x so a becomes 3, b becomes 2, c becomes 1

multiply the 1st digit of orig 2a by 3 so you get 6 (or 9 if level 3 etc)

Add y plus above to get a linear numeric value
e.g
2a becomes (2x3)+1 =7
2c becomes (2x3)+3=9
3a becomes (3x3)+1=10

etc

And then all you have to do is the conidtional formating :)

(Don't know how to do that - it wasn't really around when I was a Multiplan guru (pre-cursor to Excel :) ) )

regards

Simon

This is the approach I took when dealing with this problem at my last school... unfortunately that was 6 years ago in Office 2000 and I no longer have a copy of what I did, sorry :(
• 8th January 2009, 12:54 PM
TwyloII
thanks for the advice although I still haven't cracked it :( will post it when I have
• 9th January 2009, 01:01 PM
CESIL
try this...

Code:

```Sub update() Worksheets("Sheet1").Activate 'set first row r = 2 'set column holding target t = 2 'set column holding actual score a = 3 'select starting cell ActiveSheet.Cells(r, a).Select 'check if cell has a value Do While ActiveSheet.Cells(r, a).Value <> ""     'calculate difference from target     level = getVal(ActiveSheet.Cells(r, a).Value) - getVal(ActiveSheet.Cells(r, t).Value)         'select colour     Select Case level             Case Is > 0             ActiveSheet.Cells(r, a).Interior.Color = RGB(153, 204, 255)         Case Is = 0             ActiveSheet.Cells(r, a).Interior.Color = RGB(204, 255, 204)         Case Is > -3             ActiveSheet.Cells(r, a).Interior.Color = RGB(255, 204, 0)         Case Is > -4             ActiveSheet.Cells(r, a).Interior.Color = RGB(255, 153, 204)         Case Else             ActiveSheet.Cells(r, a).Interior.Color = RGB(153, 51, 0)     End Select         'increment counter to next row     r = r + 1 'continues to last filled row Loop End Sub Public Function getVal(score As String)     getVal = Val(Left(score, 1)) * 3 + Abs(96 - Asc(Right(score, 1))) End Function```
• 16th January 2009, 01:20 PM
TwyloII
thanks, I'll give it a try