two messages based on Conditional formated colours.

[hurfy]

hi everyone,

I have 7 cells in a column which are conditionally formatted. I'm trying to write some code based on any cells that are coloured vbRed or another message if none of them are.

I've copied most of the code from previous threads but I still cant get it to work, so any help would be greatly appreciated.

Sub test()
    Dim oneCell As Range, newNote As String


    For Each oneCell In Range("n17:n23")
        With oneCell
            If oneCell.DisplayFormat.Interior.Color = vbRed Then
                MsgBox ("please correct error from one or more cells being red")
                If newNote = "False" Then
                MsgBox ("all correct")
                    Exit For
                Else
                    .NoteText newNote
                End If
            End If
        End With
    Next oneCell
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub hurfy()
   Dim Cl As Range
   Dim Flg As Boolean
   
   For Each Cl In Range("N17:N23")
      If Cl.DisplayFormat.Interior.Color = vbRed Then
         Flg = True
         Exit For
      End If
   Next Cl
   If Flg Then
      MsgBox "please correct error from one or more cells being red"
   Else
      MsgBox "All ok"
   End If
End Sub

 

[hurfy]

Hi & welcome to MrExcel.
How about

Sub hurfy()
   Dim Cl As Range
   Dim Flg As Boolean
   
   For Each Cl In Range("N17:N23")
      If Cl.DisplayFormat.Interior.Color = vbRed Then
         Flg = True
         Exit For
      End If
   Next Cl
   If Flg Then
      MsgBox "please correct error from one or more cells being red"
   Else
      MsgBox "All ok"
   End If
End Sub

thankyou so much Fluff, it worked perfectly. clearly I need more training.

 

[Fluff]

You're welcome & thanks for the feedback