Conditional Formatting with reference to other cells and with multiple rules(in the table)

**monikiy** · 09-18-2020, 09:30 AM

Good time of the day to you all!

I have a problem with conditional formatting and really hope that someone could help

In the below table the formatting needs to be applied to the columns D, E, F
and it should be based on the values in columns A & C with the following condition:

* if cell D4 >= cell A4, cell D4 needs to turn green
* if cell D4 <= cell C4, cell D4 needs to turn red
* if cell D4 =AND(D4<A4, D4>C4), cell D4 needs to turn yellow

These 3 rules should be applied to all cells in columns D, E, F. However when I select for example column D4:D17 and set the above rules in the conditional formatting section, the colors are applied in the end, but only green and red, and it is also showing wrong colors (you can see it in the file attached).

I feel like I am missing something, an additional condition maybe or the order of the rules... I have tried to tweak the formulas, use $$ on the cells from columns A & C, but nothing seems to work.

Below I pasted a photo of the table and I also added a file where you can see the table and the conditional formatting rules that I have applied.

I would really appreciate any help, and please tell me if I didn't explain myself well and I will try again.

1600432920656.png

Many many thanks!

**TMS** · 09-18-2020, 10:08 AM

You probably need to make references to columns A and C absolute, and references to columns E to F relative. Row refernces should all be relative.

So, for yellow:

Formula:

Please Login or Register  to view this content.

for red

Formula:

Please Login or Register  to view this content.

for green

Formula:

Please Login or Register  to view this content.

And maybe tick Stop if True

Apply to cells =$D$3:$F$16

**mehmetcik** · 09-18-2020, 10:09 AM

You Applied a $ before the row number in your conditional formats so all your rules were looking at Row 3.

Should be:
=AND(D3>$C3,D3<$A3) For Yellow

**Mofasol** · 09-18-2020, 10:34 AM

I have applied conditional formatting rules in the attachment file.

So have a look the attachment.

**alansidman** · 09-18-2020, 10:53 AM

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

https://www.mrexcel.com/board/thread...table.1146093/

Conditional Formatting with reference to other cells and with multiple rules(in the table)

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Formatting with reference to other cells and with multiple rules(in the table)

Re: Conditional Formatting with reference to other cells and with multiple rules(in the ta

Re: Conditional Formatting with reference to other cells and with multiple rules(in the ta

Re: Conditional Formatting with reference to other cells and with multiple rules(in the ta

Re: Conditional Formatting with reference to other cells and with multiple rules(in the ta

Thread Information

Users Browsing this Thread

Similar Threads

Conditional Formatting Reference Multiple Cells?

Creating multiple conditional formatting rules (e.g. highlight cells with x content)

[SOLVED] Conditional Formatting with Multiple Rules (including Text & Merged Cells...eek!)

[SOLVED] Using if and And together in conditional formatting multiple rules!

Conditional Formatting, multiple rules

Conditional Formatting with multiple rules

Conditional formatting multiple rules

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions