conditional formatting (voorwaardelijke opmaak)

[Rick2807]

I have a problem with conditional formatting (see attachment).

In tab2 I created a drop down list with 3 options using Data Validation.
The 3 options (Low, Moderate and High) refer to the status of 10 locations (A1 until A10).

What I would like is that my locations A1 until A10 in tab1 change colour when the status of this location in tab2 changes
Low = Orange
Moderate = Red
High = Bleu

Using conditional formatting I managed to get this done for my first location A1.
However, I'm not able to copy this condition to all other cells.

Who can help me?

[AliGW]

First, remove the dollar sign from your CF formulae. Go from this:

=Blad2!$B$2="Low"

to this:

=Blad2!$B2="Low"

Do this for all three.

Next, in the applies to box for each CF rule, put this:

=$A$1:$A$10

All done!

[ImranBhatti]

You need to remove the $ signs from the formula in CF then you can copy the format with format painter.Since you need to paint many cells you must double click the pain painter.See the attached.

[AliGW]

Imran - sorry for this off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[ImranBhatti]

Got it.Would be taken care of in future.

[Rick2807]

Thank you AliGW!

However, when I implement it horizontally (see attachment) it does not work anymore.

Could you help me?

[AliGW]

Change the CF formulae to this:

=VLOOKUP(A$1,Blad2!$A$2:$B$11,2,0)="Low"
=VLOOKUP(A$1,Blad2!$A$2:$B$11,2,0)="Moderate"
=VLOOKUP(A$1,Blad2!$A$2:$B$11,2,0)="High"

[Rick2807]

Thank you so much!

Final question, is there a formula to make it work from bottom to top?
Like this?

A10
A9
A8
A7
A6
A5
A4
A3
A2
A1

[AliGW]

It should work either way. If not, try this:

=INDEX($B$1:$B$11,MATCH(A$1,Blad2!$A$1:$A$11,0),0)="Low"

[Rick2807]

It is not working out (see attachment)
What is it that I do wrong?

[AliGW]

A few things!

=INDEX(Blad2!$B$2:$B$11,MATCH($A1,Blad2!$A$2:$A$11,0),0)="Moderate"

Adapt the other rules to match.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FDibbins]

Got it.Would be taken care of in future.

no, you need to fix that now please