2

I have the following Excel sheet:

enter image description here

Consider the left part. I have a set of rules for changing the background colour of the whole row (but the very last cell) depending on what I write in the second cell (e.g. if I write Fer in C5 the cells from B5 to I5 are coloured in light blue).

To do this I apply the following rule:

=$C4="Fer"

to the following set of cells:

=$B$4:$I$34

Now I'd like to apply the same rule to the right part of the image too, but I can't find a way to use the same rule and it seems I have to duplicate the rule. I'd really rather not do that, as I have lots of rules and three places to apply them to.

I changed my set of cells to:

=$B$4:$I$34;$L$4:$S$31

and I unsuccessfully tried the following changes to the rule:

=OR($C4="Fer",$M4="Fer") // It says there's an error in the formula.
=OR($C4="Fer";$M4="Fer") // Accepted, but doesn't to anything.

Is there a way to solve this? Any feedback or comment is welcome!

Improve this question
2
  • So there is no way to do that with formulas, isn't it? I'll give an eye on VBA then. Commented Jan 4, 2014 at 10:06
  • Hi @pnuts I don't understand your last two comments, did I do anything wrong by posting this question? Commented Jan 6, 2014 at 15:23

1 Answer 1

Reset to default
1

Simplified example of letting 'the system' create the required new rule:

SO20763795 example

Edit This may be a way to reduce the number of your CF rules. It is not an answer to your question but may fit in with your actual requirement.

Fes, Rip and their numbering into red font might be combined into a single rule with a formula such as: 

=OR(B1="Fes",C1="Fes",B1="Rip",C1="Rip")

with Applies to =$B$1:$C$5,$L$1:$M$5:

SO20763795 second example

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi @pnuts thanks for your answer but I tried to do this and the result was that the "system" just duplicated the rule I wrote (i.imgur.com/y2PZolN.jpg)
HI @pnuts thanks again for your reply. The problem with the edited solution is that I need each string to be colored in a different color. Even "Rip" and "Fes" are not both red, but one is light red and one is dark red. That's why I'd like not to have to replicate all the rules (a dozen) for each section (three per sheet). But again thanks for your feedback and you'll probably be my accepted answer anyway.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.