VBA

[Brown Recluse]

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

 

[Lars-Åke Aspelin]

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

 

[Gary''s Student]

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.

 

[Brown Recluse]

Thanx for the reply, I'll give it a shot ~