VBA Excel to popup a message if a cell in a range is colored red

[David Montoya]

I need some assistance. The following code is not working for me, as it does not create the popup.

Sub FindColor()
    
Range("D4:P1004").Select
If ActiveCell.Interior.Color = 255 Then
    MsgBox ("Leave a Comment")
End If
End Sub

 

[David Montoya]

Rahulkr,

My solution was to use conditional formatting to color the cell or range due to an specific action, and then use the following VBA code to pop up the message

With Range("D4:D1004")
        If IsNull(.DisplayFormat.Interior.ColorIndex) Then
            MsgBox "specific message to display"
            Exit Sub
        End If
    End With

 

[Rahulkr]

Dear David,
Thank you for your help and its really appreciated, it really helped me a lot what I need, but any how, some other codes were not working properly as I have written all codes in sheet only.
Could you please help me to figure it out and improve the code. You can better understand while you will see the image, and will know what I need.

Here is the code

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("Q9") = Range("Q10") And Range("Q9") = Range("Q6") Then 'Q9 as PICKED STAFF, Q10 as Dropped Staff, Q6 as No. of Staff Coming
    
    MsgBox "!!Number of Staffs were Matched with PICK and DROP Status!!", vbOKOnly, " "
           
           
    Else
        If Range("Q2") > 0 Then
        MsgBox "!!Please PUT/REMOVE the Name of !!Out of office/IN leave staff!!"
        MsgBox "!!Check Schedule Properly or generate mail for additional car required by chosing Car Type!!", vbOKOnly, " "
    Else
        With Range("C6:E21,I6:K25") 'And Range("I6:I25") And Range("E6:E21") And Range("K6:K25")
                                    'In "=$C$6:$C$21,$I$6:$I$25,=$K$6:$K$25,$E$6:$E$21" Conditional formatting done
        If IsNull(.DisplayFormat.Interior.ColorIndex) Then
            MsgBox "!!Duplicate entries found, please check!!"
           
            Exit Sub
        End If
    End With
 
    End If
    End If
    bTemp = Application.Dialogs(xlDialogPrint).Show
    End Sub

Image

criteria
Conditions to check in sheets.

• First it will ask to check or enter Total No. of Staff
• Secondly, It will ask to enter number of staffs on leave or out of office. If number of staffs were not filled or is zero, then It will check if any staff name is already there in the field of out of office or In leave provided area and accordingly vice versa. i.e(if number of staff is not coming or out of office, then user has to put the numbers, ie, 1 or 2 and if numbers were entered in On leave/Not coming: field then, automatically pop up appears to fill staff names accordingly.
• Next it will also check number of staff coming and going with total number of staffs and also check number of staff dropped with number of Dropped staff which will be directly equal to number of staff coming and going.
• It will also check duplicate values on the basis of conditional formatting with popup message in column “C and I” and column “E and K”.
• If any how the above schedule is fully booked or not matched accordingly or some entire vehicle is booked for another work and if extra vehicle required then user can generate the pre formatted mail by click on generate of mail.
• Finally if all looks fine according to the requirement and match then final it will popup message that all looks find and popup the print window for printing.