|
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 03:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com