How do you do....it? Thread, Compare Values Above, Equal and Below in Technical; Hi OK I'm pretty good with some parts of excel but not great with macros or when it comes to ...
1. ## 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

2. 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

3. 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```

4. 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!

5. 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

6. thanks for the advice although I still haven't cracked it will post it when I have

7. 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```

8. ## Thanks to CESIL from:

TwyloII (16th January 2009)

9. thanks, I'll give it a try

SHARE:

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•