For each cell... look at a range of cells not individually

Gut reaction is that you'd need to loop through the range and count the grey cells and compare that to the varrange.Cells.Count. If the counts are equal, you can clear the colour and range, etc.

Perhaps this

    Dim done As Boolean
    If Intersect(Target, Range("D10:I10")) Is Nothing And Intersect(Target, Range("L10:L10")) Is Nothing And Intersect(Target, Range("R10:R10")) Is Nothing Then
        Cancel = True
    Else
        Set varrange = Target.Resize(POIDTBLlr1 - 9, 1)
        If Not IsNull(varrange.Interior.ColorIndex) Then
                'IF ALL CELLS IN varrange ARE GREY THEN
            If varrange.Interior.Color = 14013909 Then
                varrange.Interior.ColorIndex = xlColorIndexNone
                varrange.Offset(, 30).ClearContents
                done = True
            End If
        End If
        If Not done Then
            For Each cell In varrange
                If Not IsNumeric(cell) Then
                    Cancel = True

             Else
                    cell.Interior.Color = 14013909
                    cell.Offset(0, 30) = cell.Value
                End If
            Next
        End If
    End If

Something like:

Sub sSample()

Dim varrange As Range, cell As Range, lCount As Long

Set varrange = Range("A2:A20")

For Each cell In varrange
    If cell.Interior.Color = 14013909 Then
        lCount = lCount + 1
    End If
Next 'cell

If lCount = 0 Then
    MsgBox "no grey cells"
ElseIf lCount = varrange.Cells.Count Then
    MsgBox "all grey cells"
Else
    MsgBox "some grey cells"
End If

End Sub

Hi both,

I couldn't get the first option to work I'm afraid. The 2nd option looks more promising, but one issue...

The No GREY cells part works fine. However even when all the cells are grey, I still get the "some grey cells", rather than "all grey". To explain further, please see the below image:
Capture.PNG

1st column : No cells - WORKS
2nd column : All cells are grey (but says only some grey cells) - Not working
3rd column : Some grey cells - WORKS

I'm not sure if the reason why the 2nd column is saying only "some cells" is because the number of cells with a value doesn't match the size of the range? I'd only want to include the cells with a value in the comparison... hopefully that makes sense?

the "varrange.Cells.Count" part counts ALL cells in the range, whereas I think it needs to count only cells with a numeric value.

Thanks again

A workbook would make it much easier to provide a solution, as would a precise explanation of what is needed. The is the first time I can see that you have mentioned only populated cells being relevant- which is probably why what I suggested doesn't work for you.

I'm pleased you got it working!

You're welcome.

I was counting grey cells, not numeric cells.

... is that if ALL the cells in the range (varrange) are already coloured grey (14013909) ...