VBA conditional formatting not working

Good afternoon,
I have an excel programming book that has an example of the conditional formula I would like to use. I have modified it to fit my spreadsheet but it still doesn't work.
Basically what should happen is: If they put in a forumla 7/8 = 88% then the cell should be red, if 8/8=100% then green else no color. I tried using the conditional formatting in the too bar but it turns all cells red.

Did I put the code in the wrong spot? or am I missing something?

I have attached the spreadsheet so you can see what I am doing.

Thank you!
Larry

I am not sure what happened but when I re-opened the spreadsheet it started working however, every time a cell is clicked it runs and it seems to be cumbersome. Is there a better place to put the code?

Larry

Hello LarryC,

Here is the revised macro. When using event macros that change worksheet values be sure to disable further events and re-enable them when you code has finished.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Dim cell As Range
  Dim formulacells As Range
  
  Const REDINDEX = 3
  Const GREENINDEX = 4

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    On Error Resume Next
     'create subsets of original selection
      Set formulacells = Selection.SpecialCells(xlFormulas, xlNumbers)
        If Err.Number <> 0 Then Err.Clear
    On Error GoTo 0

     'Process the formula cells
      If Not formulacells Is Nothing Then
        For Each cell In formulacells
          If cell.Value = 1 Then
             cell.Interior.ColorIndex = GREENINDEX
          ElseIf cell.Value < 1 Then
             cell.Interior.ColorIndex = REDINDEX
          Else
            cell.Interior.ColorIndex = xlNone
          End If
        Next cell
      End If

    Application.EnableEvents = True

End Sub

Sincerely,
Leith Ross

Leith,
I do have a question: Why would you have to add those lines you did when the only event was the conditional formatting I added?

Thank you again!
Larry

Hello LarryC,

The act of changing any cell on the worksheet triggers the WorkSheet_Change() event. When you change a cell within the event procedure, the procedure is called again, and again,and again until the stack overflows and Excel becomes unstable or crashes. This known as a "Cascade Event Failure". The addition of the 2 line of code prevent the event from retriggering while the event procedure modifies cells, and switches it back on for normal operation when done.

Sincerely,
Leith Ross

Thank you for the explaination...that helps me understand it a lot more.

Not to throw a monkey wrench in there but, now they need to be able to change the color manually if they want to. if I change the color manually it automatically will change the cell back to the other color.

How do I handle that?

Larry