Conditional Formatting based on part of a dynamic range

[Sundance0315]

I have a dynamic range (sheet 2 column d) called "category" that is used as the source data for a data validation drop down list (sheet 1 column e).

The dynamic range consists of 2 types of data (inputs and outputs). Over time this range will grow as the user adds rows for new 'inputs' or 'outputs'. I have to keep all of the data in the same column in order for the drop down list to work.

The dynamic range looks something like this:
INPUTS
aaa
bbb
ccc
ddd
OUTPUTS
aaa
bbb
ccc
What I need to do now is add conditional formatting to sheet 1 column e that makes the text for inputs 'green' and the text for outputs 'red'. the challenge being that over time the list will grow and the only thing separating the two types of data is the text 'INPUTS' or 'OUTPUTS'

I was hoping to find a non VBA solution for this, but there may not be.

 

[Joe4]

Welcome to the Board!

What I need to do now is add conditional formatting to sheet 1 column e that makes the text for inputs 'green' and the text for outputs 'red'

In your example, you show the same value under both INPUTS and OUTPUTS. Is that really a possibility?
If so, in those instances, how does it know which color to choose, since the items is found under both INPUTS and OUTPUTS?

 

[Sundance0315]

Thank you for your reply. In the example the where i listed the same values under both INPUTS and OUTPUTS that would not actually happen. Each input and output is unique. Is there a way with conditional formatting to use a formula to have it look through the column and see if the text is between "Input" and "Output"? If so this would always be and "input". If the text is below the word "output" then it will always be an "Output". Does that make sense?

 

[Joe4]

Assuming that OUTPUTS always appears below INPUTS in your category list, here are the two Conditional Formatting Formulas you need (written for cell E2):

For green:
<match("outputs",sheet2!d:d,0)[ code]

=MATCH(E2,Sheet2!D:D,0) < MATCH("OUTPUTS",Sheet2!D:D,0)

For red:

=MATCH(E2,Sheet2!D:D,0) > MATCH("OUTPUTS",Sheet2!D:D,0)

</match("outputs",sheet2!d:d,0)[>