+ Post New Thread
Results 1 to 8 of 8
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. #1
    TwyloII's Avatar
    Join Date
    Dec 2007
    Location
    South East Essex
    Posts
    7
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0

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

    SimpleSi's Avatar
    Join Date
    Jun 2005
    Location
    Lancashire
    Posts
    5,829
    Thank Post
    1,476
    Thanked 594 Times in 446 Posts
    Rep Power
    169
    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. #3

    tmcd35's Avatar
    Join Date
    Jul 2005
    Location
    Norfolk
    Posts
    5,863
    Thank Post
    878
    Thanked 952 Times in 784 Posts
    Blog Entries
    9
    Rep Power
    338
    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. #4

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    169
    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. #5

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    9,989
    Thank Post
    1,852
    Thanked 2,298 Times in 1,697 Posts
    Rep Power
    819
    Quote Originally Posted by SimpleSi View Post
    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. #6
    TwyloII's Avatar
    Join Date
    Dec 2007
    Location
    South East Essex
    Posts
    7
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Unhappy

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

  7. #7

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    169
    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. #8
    TwyloII's Avatar
    Join Date
    Dec 2007
    Location
    South East Essex
    Posts
    7
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    thanks, I'll give it a try

SHARE:
+ Post New Thread

Similar Threads

  1. moodle ldap default values if not set by AD
    By russdev in forum Virtual Learning Platforms
    Replies: 12
    Last Post: 8th June 2010, 11:46 AM
  2. Passing combo box values to datagrid view
    By Shrimpersfan in forum Coding
    Replies: 1
    Last Post: 7th January 2009, 11:17 AM
  3. Exchange 2007 > *SPAM* SCL values
    By Ben_Stanton in forum Windows
    Replies: 2
    Last Post: 13th November 2008, 01:58 PM
  4. Excel Not Recognizing Cells of Equal Value
    By G_Money in forum Windows
    Replies: 2
    Last Post: 21st June 2007, 08:33 AM
  5. ADM Template to set hkey_users.default reg values
    By ryan_powell in forum Windows
    Replies: 1
    Last Post: 2nd December 2006, 08:38 PM

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Posting Permissions

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