• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Conditional Formatting Activated, VBA

I've scoured the web for an answer to this question but came up with nothing. I'm trying to have a message box popup if a conditional format is activated on a cell, each cell has 3 different conditional formats, each with a different validation message. Since conditional formatting does not set the interior property, I cannot use ColorIndex as a criteria. Below is what I have, I just need to replace the "Interior.ColorIndex" with, whichever, if any function lets me select the conditional formatting criteria (which is colors.) Below is a snapshot of what I have - Thanks much.

If Cells(29, 5).Interior.ColorIndex = 3 And Cells(43, 2).Interior.ColorIndex = 6 Then
MsgBox "-Line 1 of Message" & vbCrLf & _
        "-Line 2 of message.", vbOKCancel, "Title
       
ElseIf Cells(43, 2).Interior.ColorIndex = 6 Then
MsgBox "Please provide an explanation for question 5a."
0
shoaibs
Asked:
shoaibs
  • 3
  • 3
1 Solution
 
StephenJRCommented:
Use the underlying CF criteria.
0
 
shoaibsAuthor Commented:
I would, but they are quite complex with nested if's etc., I would hate to have to reinput them all into VBA. Any other ideas?
0
 
StephenJRCommented:
There is no other way. If you post a workbook we can get an idea of what you're doing.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
shoaibsAuthor Commented:
Thanks for the advice. Below is one of the validation formulas, and my conversion to VBA- but this time around it did not work, any idea of what I'm doing wrong in the below example?

=IF(AND(J41="yes",ISBLANK(B43)),TRUE,FALSE)

Private Sub worksheet_deactivate()
If Cells(43, 2).Value = Null And Cells(10, 41).Value = "Yes" Then
MsgBox "-Please provide an explanation for question 5a.", vbOKCancel
End If
End Sub

Thanks again.
0
 
StephenJRCommented:
Try this:

Private Sub worksheet_deactivate()
If IsEmpty(Cells(43, 2)) And Cells(41, 10).Value = "Yes" Then
MsgBox "-Please provide an explanation for question 5a.", vbOKCancel
End If
End Sub
0
 
shoaibsAuthor Commented:
Actually, replaced the null with "" and it worked. Thanks again for all of your help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now