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