two messages based on Conditional formated colours.

hurfy

New Member
Joined
Feb 28, 2019
Messages
4
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.:)
Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,181
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Code:
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

New Member
Joined
Feb 28, 2019
Messages
4
Hi & welcome to MrExcel.
How about
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,181
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,789
Messages
5,524,885
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top