Conditional Formatting with multiple conditions

[D_Landry]

Hello! I'm in need of your expertise!

On the Customer Summary tab, I need columns H and Q to have conditional formatting (highlight red with dark red text).

Here are the conditions:

Columns A & C need to be matched to the 'Cust #' and 'Dept' on 'Store Volume code' tab (Columns B & D), with the conditional formatting to be based on the values in Column E (less than or equal to).

Thanks in advance for your assistance!

D. Landry

[Special-K]

Try this (somewhat untested)

Select H4

Conditional Formatting
New Rule
Use a formula to determine...

=AND(A4 <> "",H4 <> "",H4 < INDEX(IF(A4='Store volume code'!B$2:B$16,'Store volume code'!D$2:E$7,IF(A4='Store volume code'!B$18:B$28,'Store volume code'!D$18,E$23)),MATCH(C4,IF(A4='Store volume code'!B$2:B$16,'Store volume code'!D$2:D$7,IF(A4='Store volume code'!B$18:B$28,'Store volume code'!E$18,E$23))),2))
Format as required

Use Format painter (paintbrush icon) to copy to other cells

For column Q just change the H references above to Q

[D_Landry]

Thanks Special-K. Unfortunately, it's not working on all items. Some cells are doing what they should, others are not. What is also weird, I have it formatted for Fill and Text in red, but only Text is showing red - even though in the Rules Manager box it shows Fill and Text Red.

[JeteMc]

This solution employs a helper column (V), which may be hidden for aesthetic purposes.
The formula that populates the helper column is:

Please Login or Register  to view this content.

The conditional formatting rule for column H is:

Please Login or Register  to view this content.

Note: to get the conditional formatting to work I first chose the font color, on the fill tab I first selected the "No Color" button and then chose a color.
Let us know if you have any questions.

[D_Landry]

Thank you so much JeteMC - this is really great!! I very much appreciate your assistance.

-D. Landry

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.