Data Validations for specific criteria

[dougebowl]

I need code to perform 2 data validations:

Here are the two sheets contained in my Workbook:

Sheet 1 contains 3 colums of data	Column	Data Structure
Test Plan ID	A	One Value
Processes	B	No Value OR Single Value OR Concatenated IDs using semi-colon delimiter ";"
Controls	C	No Value OR Single Value OR Concatenated IDs using semi-colon delimiter ";"

Sheet 2 contains 6 columns of data	Column	Data Structure
Process ID	A	Single Value
Process Name	B	Single Value
Status	C	Single Value
Control ID	D	No Value OR Single Value
Control Name	E	No Value OR Single Value
Status	F	No Value OR Single Value

First, I need code to first validate if ALL the values on Sheet 1 in columns B and C are Active values

• Column B determined if "Active" is populated in column C on Sheet 2
• Column C determined if "Active" is populated in Column F on Sheet 2

This would be looking up the value(s) in Sheet 1 for the corresponding value in Sheet 2 and determining if the value is in an Active Status
If the status is found to be "Inactive", I would like the specific value in column B or C to display in 'Red' Font color

Second, I need code to validate there are no conflicting relationships

• Sheet 2 basically has the relationships between the Processes and Controls
• Each row represents a relationship
• A Process can have many Controls related to it (one-to-many relationship)
• A Control can only be related to one Process (one-to-one Relationship)

Related Processes and Controls cannot appear in columns B and C on Sheet 1
If this conflict is identified, I would like the font color for the 2 values (the Process and the Control) to be "Dark Green"

Example: Process "123" and Control "A" are listed in columns B and C respectively
Process "123" is related to Controls "A, B, and C"
Therefore Process 123 and Control A would be in "Dark Green" color as this would be considered a conflict.

 

[dougebowl]

Bump