Conditional Formatting for two criteria

[SRMPURCHASE]

The main intent is for J14 to change colors when:
A14:A200 = any value (10 choices) in the range $G$1:$G$10 and;

J14:J200 <= to any value (10 choices) in range $H$1:$H$10

I currently use the following formula repeated 10 times for each combination in G and H ranges.

FOR CELL J14 "AND($A14=$G$1,$J14<=$H$1)

Changing the G and H row references for each of the 10 CF formulas.

Is there a more efficient way to express this in a single formula?

 

[etaf]

i could do it with 2 helper columns , using a countif( $G$1:$G$10, A14) and then countif( $H$1:$H$10, J14) in 2 columns , now as a conditional formatting rule - all you need is a (SUM(helper column 1 ) +Sum(helper column 2 ) >0
not a great way

I'm sure there is a way to see if a range exists within another range and recall doing this before BUT i cant find the info

VBA would do it I'm sure

J14 is one of your ranges - is that where you want the cell to change colour

i'll see if i can find the function to check a list within another list - i'm sure there is

 

[SRMPURCHASE]

I thought of concatenating a couple of columns to come up with a unique identifier to get the result I want, still working that out.
Sounds like your idea is similar.

Yes, Column J ( only J14:J200) will change color as the conditions are met

 

[etaf]

is that the whole of column J14 to J200 ?
I thought it was just 1 Cell

so for J14:J200 to change colour then that Cell J14, J15 etc MUST exist in H1:H10
simple countif() would do that
BUT the AND is that A14:A200 has to be in cell G1:G10

Lets assume only 1 value exists in J - and that is J100 matches a value in H1:H10 - so TRUE

Now what happens in A

can A14 match G1:G10 - and highlight - If so then Highlight just J100 as that matches and somewhere in column A theres a match to G

OR does A100 have to have a match with G1:G10 to be TRUE

just trying to clarify

Perhaps some examples may help

OR use XL2BB and show manually the results expected and why
or link to a share , like onedrive, dropbox, google sheets (thats all i would look at)

 

[ravi2628]

Can you share sample Data to get the Logic?

 

[SRMPURCHASE]

I solved this issue another way, that concept was proving too cumbersome, appreciate all the interest.

 

[etaf]

perhaps fpr other people searching google or here , you could put the solution which solved the issue.
nothing more frustrating then to find the same issue one is trying to solve only to see a thread with no conclusion

 

[SRMPURCHASE]

I inserted an INDEX

 

[SRMPURCHASE]

oops
I inserted an index lookup to match the mileage value with each truck description instead of tying the formula to the table in Rows 1-10
Then I set an IF condition formatting to change the color of the calculated mileage reading it is was less than the current mileage telling the maint shop if that vehicle had maintenance due to perform.