Solved

Conditional Formatting Activated, VBA

Posted on 2011-02-10
6
252 Views
Last Modified: 2012-08-14
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
Comment
Question by:shoaibs
  • 3
  • 3
6 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Use the underlying CF criteria.
0
 

Author Comment

by:shoaibs
Comment Utility
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
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
There is no other way. If you post a workbook we can get an idea of what you're doing.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:shoaibs
Comment Utility
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
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
Comment Utility
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
 

Author Comment

by:shoaibs
Comment Utility
Actually, replaced the null with "" and it worked. Thanks again for all of your help.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now