Compare Values Above, Equal and Below

[TwyloII]

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

[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

[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).

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

[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!

[elsiegee40]

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

[TwyloII]

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

[CESIL]

try this...

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

[TwyloII]

thanks, I'll give it a try