![]() | Register | FAQ | Members | Social Groups | User Map | Calendar | Search | Today's Posts | Mark Forums Read |
How do you do....it?
How do you do it? forum sponsored by |
| ||
| | | LinkBack | Thread Tools | Search Thread |
| Sponsored Links |
| | #1 |
![]() Join Date: Dec 2007 Location: South East Essex
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Rep Power: 0 ![]() | 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 ????: EduGeek.net Forums http://www.edugeek.net/forums/how-do-you-do/28611-compare-values-above-equal-below.html if more than 3 increments below their target, red more than 6 points below their target, brown I hope this makes sense! AJ |
| |
| | #2 |
![]() Join Date: Jun 2005 Location: Lancashire
Posts: 1,895
Thanks: 137
Thanked 97 Times in 81 Posts
Rep Power: 28 ![]() ![]() ![]() ![]() ![]() | 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 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 |
![]() Join Date: Jul 2005 Location: Norfolk
Posts: 1,286
Thanks: 102
Thanked 117 Times in 106 Posts
Blog Entries: 9 Rep Power: 34 ![]() ![]() ![]() ![]() ![]() ![]() ![]() | 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 |
![]() Join Date: Nov 2006 Location: Hampshire
Posts: 240
Thanks: 2
Thanked 21 Times in 17 Posts
Rep Power: 9 ![]() ![]() | 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 | |
![]() Join Date: Jan 2007 Location: Kent
Posts: 2,293
Thanks: 178
Thanked 133 Times in 123 Posts
Rep Power: 34 ![]() ![]() ![]() ![]() ![]() ![]() ![]() | Quote:
| |
| |
| | #6 |
![]() Join Date: Dec 2007 Location: South East Essex
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Rep Power: 0 ![]() | thanks for the advice although I still haven't cracked it |
| |
| | #7 |
![]() Join Date: Nov 2006 Location: Hampshire
Posts: 240
Thanks: 2
Thanked 21 Times in 17 Posts
Rep Power: 9 ![]() ![]() | 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
|
| |
| The Following User Says Thank You to cadjs For This Useful Post: | TwyloII (16-01-2009) |
| | #8 |
![]() Join Date: Dec 2007 Location: South East Essex
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Rep Power: 0 ![]() | thanks, I'll give it a try |
| |
| | ||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Passing combo box values to datagrid view | Shrimpersfan | Coding | 1 | 07-01-2009 11:17 AM |
| Exchange 2007 > *SPAM* SCL values | Ben_Stanton | Windows | 2 | 13-11-2008 01:58 PM |
| moodle ldap default values if not set by AD | russdev | Virtual Learning Platforms | 7 | 13-03-2008 03:32 PM |
| Excel Not Recognizing Cells of Equal Value | G_Money | Windows | 2 | 21-06-2007 08:33 AM |
| ADM Template to set hkey_users.default reg values | ryan_powell | Windows | 1 | 02-12-2006 08:38 PM |
| Tags |
| excel cell comparisons |
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search Thread |
| |











