Excel 2010 - Conditional Forwarding needs to extend to other cells

Question

Hello there,

I have a spreadsheet with multiple Conditional Formatting rules contained within one column, and I was wondering if there was a way to extend the formatting across to another column (without the other column having the actual rule description).

For example, Column A below has multiple text based rules. If a cell contains the word "some" - filled it red. If a cell contains the word "more" - fill it blue.

I want the fill colour to extended to the cell on the right also. So, below, 12 will also be red, and 22 will be blue. I hope that makes sense. 

Column A	Column B
Some Text (filled red)	12 (also needs to be filled red)
More Text (filled blue)	22 (also needs to be filled blue)

If there is a way to do this, within Excel 2010, please let me know how.

Many Thanks

Mark

Answer 1

You can list conditional formatting rules, see for example http://dailydoseofexcel.com/archives/2010/04/16/listing-format-conditions/

Answer 2

Many thanks for the quick response.

The issue I have now: there are manyconditional rules set. Would there be a way to export a list of rules from Excel, as I would need to start over and this would take a huge amount of time.

Answer 3

Start by removing the existing conditional formatting rule.

Let's say that you want to apply the conditional formatting to A2:B100.

Select this range.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format.

Enter the formula

=ISNUMBER(SEARCH("some",$A2))

It is essential to have $ before the column letter A.

Click Format...

Activate the Fill tab.

Select red, then click OK twice.

Repeat these steps, but with

=ISNUMBER(SEARCH("more",$A2))

and the color blue.