Conditional Formatting with multiple conditions

**D_Landry** · 11-10-2016, 12:02 PM

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** · 11-10-2016, 01:04 PM

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** · 11-10-2016, 02:12 PM

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** · 11-10-2016, 08:40 PM

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

Formula:

=IFERROR(IF(ISNUMBER(MATCH(A4,'Store volume code'!B$2:B$16,0)),INDEX('Store volume code'!E$2:E$7,MATCH(C4,'Store volume code'!D$2:D$7,0)),IF(MATCH(A4,'Store volume code'!B$18:B$28,0),INDEX('Store volume code'!E$18:E$23,MATCH(C4,'Store volume code'!D$18:D$23,0)),"")),"")

The conditional formatting rule for column H is:

Formula:

=AND(H4<>"",V4<>"",H4<=V4)

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** · 11-11-2016, 01:11 PM

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

-D. Landry

**JeteMc** · 11-11-2016, 01:13 PM

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.

Conditional Formatting with multiple conditions

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View