Macro not working

[hartless43]

Sub ClearRedFontsMistakes()
    Dim rng As Range
    Dim cell As Range
    Dim redCount As Long
    
    redCount = 0
      
    Set rng = Range("B2:J10")
    
    
    For Each cell In rng
        If cell.DisplayFormat.Font.ColorIndex = 3 Then
            redCount = redCount + 1
        cell.ClearContents
        End If
    Next cell
      
    Range("L10").Value = Range("L10").Value + redCount
    
End Sub

OK, all you experts, an armature needs some help.

The above Sub keeps track of Mistakes which will be any Red Font. When playing the Sudoku game any number entered that does not match up with the Solution it will turn Red. Then the Macro Erase Mistakes (the above code) will add the number of mistakes made to cell L10 and then remove the red Fonts. The last line in this sub keeps a running total of mistakes. This has been working fine and thanks to Fluff for part of the code. This has been working as designed, but now I have a little problem. Cell L10 no longer shows the number of Mistakes. I figured the color of the cell in L10 was changed, but it’s not, but when I click on the “empty” L10 I get the following. A number appears in the Formula Bar but not in L10 and when I hover my cursor over cell L10 a small box with rounded corners will appear and will have the same number as the Formula Bar. Just so happens this number is the remaining numbers to be played and if I do another mistake that number will double. I could not capture a picture of that box with the rounded corners, so does anyone know how it got there and how to get rid of it. I probably click on something in the task bar and cause this.



Thanks

 

[DanteAmor]

If it's a shape or an image, simply select it and delete it.
I tested the code and it works just as you describe.
Also check that the font color of cell L10 is black and that the cell format is number.

 

[hartless43]

DanteAmor, got it deleted and the other problems I mentioned are still there. And yes, the cell color is black and formatted as a number. Thanks for your help and will wait to see if anyone else can help.

 

[DanteAmor]

Check that the custom format is in general or copy any other cell and paste it on top of cell L10 to eliminate anything it has

 

[hartless43]

Check that the custom format is in general or copy any other cell and paste it on top of cell L10 to eliminate anything it has


View attachment 121090

Didn't have to use any of above except the Number, the problem was I had cell L10 highlighted in yellow with a black font and that was the reason it was not shown in cell L10 but only on the Formula Bar and the color was also yellow. Anyway, that's not the problem. Why is it picking up the remaining numbers not used? I did a through research of all modules, macros and there is not any that says "put the remaining numbers in cell L10". The about macro is the only one that refers to or places data in cell L10. I guess will have to go back to the CountByFontColor. Thans again

 

[DanteAmor]

If cell.DisplayFormat.Font.ColorIndex = 3 Then

That line counts the cells that have a color but that was set with conditional formatting, is that what you need?

 

[hartless43]

That line counts the cells that have a color but that was set with conditional formatting, is that what you need?

 

[hartless43]

Yes, that is correct. If any number entered does not match the solution it will turn red. Fluff gave me the Display line because I could not find a way to delete red fonts. Anyway, still trying to figure out why cell L10 is picking up the remaining numbers.

 

[hartless43]

DantaAmor, I stripped down my program to only a Game and a Solution and using the Marco at top of page I was getting the same errors, so I found the original I had posted on another forum, and it works, so for some reason the above Marco is causing the problem. Below is the original macro

Sub ClearRedFontsMistakes()

Dim rng As Range
Dim cell As Range
Dim RedFontsError As Boolean
Set rng = Range("B2:J10")
RedFontsError = True


For Each cell In rng
If cell.DisplayFormat.Font.ColorIndex = 3 Then
Exit For
End If
Next cell

For Each cell In rng
If cell.DisplayFormat.Font.ColorIndex = 3 Then
cell.ClearContents
End If
Next cell

'/ Below will prevent the Macro from running if no Red Fonts
'/ are found and a count will not be added to L10

If RedFontsError = False Then
Exit Sub
End If

Range("L10").Value = Range("L10").Value + 1
End Sub

Thanks for your help and will close this out as solved

 

[MARK858]

That line counts the cells that have a color but that was set with conditional formatting

Just to say DisplayFormat.Font.ColorIndex counts the Font colored with conditional formatting or regular formatting, it is literally the Font color as displayed by whatever method used.