IF AND problem

[mcphatt]

I am a Maths Teacher and I have been trying to teach myself to write vba code and macros in order to make small maths puzzles for my students in lessons. I apologise for my very basic skills in vba but I hope you will help anyway.

I am trying to write a vba code that IF cells in a range (C4:I9) have no fill (x1None) AND the text content in these cells (only those which have no fill) does not match any other cell (which has no fill) to fill that cell with a certain colour.

Here is what I have tried but to no avail:

Sub Undo()
'
' Undo Macro
'
'
Range("C4:I9").Select
If cell.Interior = x1None And strcmp(C4, I9, vbTextCompare) = 0 Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
ActiveSheet.Range("IV65536").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Else
' do nothing
End If
End Sub

I know I am way off with the If And statement, but bear with me I am very very new to all this!

Hope someone can help.

Thanks

 

[Scott Huish]

First, it's not x1none, it's xlNone.

There is no need to select and you also refer to cell which is never defined.

Sub test()
Dim c As Range, d As Range
For Each c In Range("C4:I9")
    If c.Interior.ColorIndex = xlNone Then
        If d Is Nothing Then
            Set d = c
        Else
            Set d = Union(d, c)
        End If
    End If
Next
If Not d Is Nothing Then
    For Each c In d
        If Application.WorksheetFunction.CountIf(Range("C4:I9"), c) = 1 Then
            c.Interior.ColorIndex = 3
        Else
            c.Interior.ColorIndex = xlNone
        End If
    Next
End If
End Sub

 

[mcphatt]

Thanks so much for the reply HOTPEPPER,

Unfortunately, and probably due to something I am not doing, it doesn't seem to be working.

I have copy and pasted the code into a module and assigned to macro to a button and it runs fine with no error, but it doesn't change the format of any cells.

Does it matter that the font of the text in the cells is wingdings?

Or also that the text in the cells is the result of a formula?

Sorry to be such a pain! and thanks again for the help so far.

 

[mcphatt]

I have run some tests and the code is good, it seems that the problem is more with the fact that the cell already is filled with a blue fill. If I remove that fill from all the cells it works brilliantly, so thanks.

Unfortunately for the game to work I need the cells to be that colour until they are revealed.

Is there any way I can change the code to make it work with the cells having different fills (some with blue others revealed (xlNone)?

Thanks again.

 

[mcphatt]

I have now isolated the issue:

The code is checking for equal text in the whole range not just the cells with xlNone, can I make the code count only the cells with no fill so that even if there is another cell with the same text, because that cell is not xlNone, it will still turn the xlNone cell red?

Hope that makes sense.

Thanks again to HOTPEPPER, and sorry for all the previous posts that didn't isolate the issue!

Thanks!!

 

[Scott Huish]

Sorry, try this:

Sub test()
Dim c As Range, d As Range
For Each c In Range("C4:I9")
    If c.Interior.ColorIndex = xlNone Then
        If d Is Nothing Then
            Set d = c
        Else
            Set d = Union(d, c)
        End If
    End If
Next
If Not d Is Nothing Then
    For Each c In d
        If Application.WorksheetFunction.CountIf(d,c) = 1 Then
            c.Interior.ColorIndex = 3
        Else
            c.Interior.ColorIndex = xlNone
        End If
    Next
End If
End Sub

 

[mcphatt]

Thanks again HOTPEPPER,

I think we are getting closer.

This code works in part. If I have only one cell with xlNone, as it has no pair, so it turns it red. So that is fine but when I have two cells with no fill (xlNone) there is a Runtime error '1004' "Unable to get the CountIf property of the WorksheetFunction class"

If I have several cells xlNone with the rest filled, it works once, but If I reveal more, it has the same error.

Not sure what the error means?

(Also, is there any way to find out the number for the colour fill I have the other cells for the code (instead of 3 for red) as it would be good if they could turn the Blue I have them - this is the macro code to fill with the blue
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0

Thanks again for your help with the code!

 

[mcphatt]

Oh, and I just wanted to say again that I really appreciate the help!

 

[Scott Huish]

I can run that code on an entirely blank range with no problem. Can you give a more specific example of where you are running into the problem?

 

[mcphatt]

There are two specific problems,

The first is that if I reveal two cells (xlNone) which are not next two eachother then the code gives that error. It works fine for one square and for two next to eachother.

The second is if I reveal cells and the code fills some with red and leaves the matching pairs. When I then reveal other cells which are not matching and try to run the code again I get that error.

Thanks for your continued help HOTPEPPER.