Sub test() Dim j As Long, r As Range, c As Range 'suppose the conditional format of green is "the cell value is >10" Set r = Range("A1:a25") For Each c In r If c > 10 Then j = j + 1 Next c MsgBox "the no. of cells turned green due to being >10 is " & j End Sub