|
[x]
Posted via EE Mobile
|
|
| Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again. |
|
|
|
|
Asked by pete47 in Microsoft Excel Spreadsheet Software
can the code below be adapted so that instead of letters being compared and coloured red, yellow or green, numbers are. numbers from 1-9. 9 being the highest, 1 being the lowest. some numbers will appear with a letter attached, like 5a. in that case 5b would be lower and therefore coloured red. 5a, 5b, 5c 5a is high, 5c is low
5a-5a = yellow
5c-5a coloured green
5c-6 coloured green
4 - 6 = green
6-4 = red.
hope ive been clear. im knackered at the moment
pete
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
|
Sub colorz()
Application.ScreenUpdating = False
For i = 16 To 200
For j = 5 To 102 Step 3
Cell1 = Cells(i, j)
Cell2 = Cells(i, j + 1)
If Cell1 = "" Or Cell2 = "" Then
Cells(i, j).Interior.color = vbWhite
Cells(i, j + 1).Interior.color = vbWhite
GoTo Skip
End If
If Len(Cell1) > 1 Then
A = 255 - Asc(Left(Cell1, 1)) + Asc(Mid(Cell1, 2, 1)) / 1000
Else
A = 255 - Asc(Left(Cell1, 1))
End If
If Len(Cell2) > 1 Then
b = 255 - Asc(Left(Cell2, 1)) + Asc(Mid(Cell2, 2, 1)) / 1000
Else
b = 255 - Asc(Left(Cell2, 1))
End If
If A > b Then
Cells(i, j).Interior.color = vbRed
Cells(i, j + 1).Interior.color = vbRed
End If
If A = b Then
Cells(i, j).Interior.color = vbYellow
Cells(i, j + 1).Interior.color = vbYellow
End If
If A < b Then
Cells(i, j).Interior.color = vbGreen
Cells(i, j + 1).Interior.color = vbGreen
End If
Skip:
Next j
Next i
Application.ScreenUpdating = True
End Sub
|
20091021-EE-VQP-81 - Hierarchy / EE_QW_3_20080625
