Expert: Tom Ogilvy - 11/8/2009

Question
Evening,
I'm 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).  Don't know how to get VBA to read the formated percent that I have in the Excel file and I can't seem to get the proper verbiage for a range (60 to 70...) ~ hair loss is imminent.   This is what I have, but it is not working.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

Set MyPage = Range("$e$2:$bn$63")
   For Each Cell In MyPage
   
       If Cell.Value = 0 Then
           Cell.Interior.ColorIndex = 15
       End if
       If Cell.Value > 76 < 89 Then
           Cell.Interior.ColorIndex = 35
       End if
       If Cell.Value > 89 Then
           Cell.Interior.ColorIndex = 43
       End if
       If Cell.Value > 60 < 75 Then
           Cell.Interior.ColorIndex = 27
       End if
       If Cell.Value > 50 < 60 Then
           Cell.Interior.ColorIndex = 45
       End if
       If Cell.Value > 1 < 50 Then
           Cell.Interior.ColorIndex = 45
       End If

  Next
End sub
--
Regards ~ David


Answer
David,

percent values are stored as decimal values if the cell actually holds a number and you have it formatted to display percent (rather than having the string "80%").  80% is stored as .80

You can see this by selecting the cell and formatting it as general.  

so in  your could you would do

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

Set MyPage = Range("$e$2:$bn$63")
  For Each Cell In MyPage
  
      If Cell.Value = 0 Then
          Cell.Interior.ColorIndex = 15
      End if
      If Cell.Value >= .76 and cell.value  < .89 Then
          Cell.Interior.ColorIndex = 35
      End if
      If Cell.Value >= .89 Then
          Cell.Interior.ColorIndex = 43
      End if
      If Cell.Value >= .60 and cell.Value < .75 Then
          Cell.Interior.ColorIndex = 27
      End if
      If Cell.Value >= .50 and cell.value < .60 Then
          Cell.Interior.ColorIndex = 45
      End if
      If Cell.Value >= .01 and cell value < .50 Then
          Cell.Interior.ColorIndex = 45
      End If

 Next
End sub


--
Regards,
Tom Ogilvy