|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 8th, 2009, 10:01 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
VBA
Trying to circumvent the conditional format limit of 3 in Excel 03. I have
a file with various percents and want to color code each percent threshold. For instance, if it is 60 to 75% color code it 27 (excel color code). This is what I have, but it is not working ~ hair loss is immenent: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Set MyPage = Range(("$e$2:$bn$63"), "%0") For Each Cell In MyPage If Cell.Value = 0 Then Cell.Interior.ColorIndex = 15 Else If Cell.Value 76 89 Then Cell.Interior.ColorIndex = 35 Else If Cell.Value 89 Then Cell.Interior.ColorIndex = 43 Else If Cell.Value 60 75 Then Cell.Interior.ColorIndex = 27 Else If Cell.Value 50 60 Then Cell.Interior.ColorIndex = 45 Else If Cell.Value 1 50 Then Cell.Interior.ColorIndex = 45 End If Next -- Regards 
|
|
#2
November 8th, 2009, 10:34 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA
On Sun, 8 Nov 2009 14:01:39 -0800, Brown Recluse
wrote: Trying to circumvent the conditional format limit of 3 in Excel 03. I have a file with various percents and want to color code each percent threshold. For instance, if it is 60 to 75% color code it 27 (excel color code). This is what I have, but it is not working ~ hair loss is immenent: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Set MyPage = Range(("$e$2:$bn$63"), "%0") For Each Cell In MyPage If Cell.Value = 0 Then Cell.Interior.ColorIndex = 15 Else If Cell.Value 76 89 Then Cell.Interior.ColorIndex = 35 Else If Cell.Value 89 Then Cell.Interior.ColorIndex = 43 Else If Cell.Value 60 75 Then Cell.Interior.ColorIndex = 27 Else If Cell.Value 50 60 Then Cell.Interior.ColorIndex = 45 Else If Cell.Value 1 50 Then Cell.Interior.ColorIndex = 45 End If Next Try this macro: Private Sub Worksheet_Change(ByVal Target As Range) Set MyPage = Range("$E$2:$BN$63") For Each cell In MyPage If cell.Value = 0 Then cell.Interior.ColorIndex = 15 If cell.Value = 1 Then cell.Interior.ColorIndex = 45 If cell.Value = 50 Then cell.Interior.ColorIndex = 45 If cell.Value = 60 Then cell.Interior.ColorIndex = 27 If cell.Value = 76 Then cell.Interior.ColorIndex = 35 If cell.Value = 89 Then cell.Interior.ColorIndex = 43 Next End Sub Hope this helps / Lars-Åke
|
|
#3
November 8th, 2009, 11:07 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA
Build MyPage as follows:
Set MyPage = Nothing For Each r In Range("E2:BN63") If r.NumberFormat = "0%" Then If MyPage Is Nothing Then Set MyPage = r Else Set MyPage = Union(MyPage, r) End If End If Next Then use it in your For loop. -- Gary''s Student - gsnu200908 "Brown Recluse" wrote: Trying to circumvent the conditional format limit of 3 in Excel 03. I have a file with various percents and want to color code each percent threshold. For instance, if it is 60 to 75% color code it 27 (excel color code). This is what I have, but it is not working ~ hair loss is immenent: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Set MyPage = Range(("$e$2:$bn$63"), "%0") For Each Cell In MyPage If Cell.Value = 0 Then Cell.Interior.ColorIndex = 15 Else If Cell.Value 76 89 Then Cell.Interior.ColorIndex = 35 Else If Cell.Value 89 Then Cell.Interior.ColorIndex = 43 Else If Cell.Value 60 75 Then Cell.Interior.ColorIndex = 27 Else If Cell.Value 50 60 Then Cell.Interior.ColorIndex = 45 Else If Cell.Value 1 50 Then Cell.Interior.ColorIndex = 45 End If Next -- Regards
|
|
#4
November 9th, 2009, 02:10 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA
Thanx for the reply, I'll give it a shot ~
 -- Regards "Gary''s Student" wrote: Build MyPage as follows: Set MyPage = Nothing For Each r In Range("E2:BN63") If r.NumberFormat = "0%" Then If MyPage Is Nothing Then Set MyPage = r Else Set MyPage = Union(MyPage, r) End If End If Next Then use it in your For loop. -- Gary''s Student - gsnu200908 "Brown Recluse" wrote: Trying to circumvent the conditional format limit of 3 in Excel 03. I have a file with various percents and want to color code each percent threshold. For instance, if it is 60 to 75% color code it 27 (excel color code). This is what I have, but it is not working ~ hair loss is immenent: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Set MyPage = Range(("$e$2:$bn$63"), "%0") For Each Cell In MyPage If Cell.Value = 0 Then Cell.Interior.ColorIndex = 15 Else If Cell.Value 76 89 Then Cell.Interior.ColorIndex = 35 Else If Cell.Value 89 Then Cell.Interior.ColorIndex = 43 Else If Cell.Value 60 75 Then Cell.Interior.ColorIndex = 27 Else If Cell.Value 50 60 Then Cell.Interior.ColorIndex = 45 Else If Cell.Value 1 50 Then Cell.Interior.ColorIndex = 45 End If Next -- Regards
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
