Conditional formatting between columns in diferent worksheets

[matiati]

Hello!

I've trying to apply conditional formatting to a column but not succeeded. I get the message "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."

Conditional formatting.xlsx

Could someone help me?

Thanks

[zbor]

Please upload example workbook in the forum.

Click Edit -> Go advanced and then click Paper clip icon and upload file.

[Fotis1991]

Hi and welcome to the forum

For use Conditional Formatting, ragarding another workbook, you must Give Names to the ranges.

If you want, upload a sample workbook to the forum. Not a link.

Hope to helps you.


@Zbor.Ιt looks, to worked at the same time..

[matiati]

Thanks for your replies and sorry for the link, I have already fixed it.

Regards!

[Fotis1991]

...By whitch conditions??

[matiati]

Not equal, that's why A7 and A13 should be highlighted...

[zbor]

For CF on other sheet you must define names and then create rule by that name.

or you can add extra column in Sheet1 and then make CF by that column.

[matiati]

Thanks for your time zbor.

I don't understand the procedure to "create rule by that name" and how "=$A1<>INDEX(Column_A,ROW($A1))" appeared

[Fotis1991]

Take a look to the example,

Hope to helps you.

[zbor]

I don't understand the procedure to "create rule by that name" and how "=$A1<>INDEX(Column_A,ROW($A1))" appeared

Select one cell, few cells or whole row or column.

For example, select from A3 to A10.

In upper left corner you will see A3

Instead of A3 write some name like: test

Now, your range (A3:A10) is named test and you can make for example =COUNTA(test) and get some number same as =COUNTA(A3:A10)

That name (in my example Column_A) I gave to range A:A in Sheet2 and I create rule by that name

[Fotis1991]

Great work Zbor!!

[matiati]

Select one cell, few cells or whole row or column.

For example, select from A3 to A10.

In upper left corner you will see A3

Instead of A3 write some name like: test

Now, your range (A3:A10) is named test and you can make for example =COUNTA(test) and get some number same as =COUNTA(A3:A10)

That name (in my example Column_A) I gave to range A:A in Sheet2 and I create rule by that name

zbor, that's was clear for me, what I don't get is the creating rule procedure. Let's go step by step:

1°) In worksheet Column1 name the range A2:A13 test1
2°) In worksheet Column2 name the range A2:A13 test2
3°) Conditional formatting > Manage rules... > New Rule...
4°) Select a Rule Type: Use a formula to determine which cells to format, Format values where this formula is true: "test1" <> "test2", Format: fill blue.

That procedure doesn't work, I'm doing something wrong at step 4.

Thanks!

[zbor]

Yes, last procedure doesn't work.

You see, you must compare value in first sheet (A1, A2, A3...) with same value in other sheet (A1, A2, A3).

First value is: $A1
Second value is: INDEX(Column_A,ROW($A1)) <---- Column_A is test2 in above example

CF criteria is: =$A1<>INDEX(Column_A,ROW($A1))

[matiati]

zbor, following these steps:

1°) In worksheet Column2 name the range A2:A13 test2
2°) In worksheet Column1 select the range A2:A13
2°) Conditional formatting > Manage rules... > New Rule...
4°) Select a Rule Type: Use a formula to determine which cells to format, Format values where this formula is true: =$A2<>INDEX(test2,ROW($A2)) , Format: fill blue.


It didn't work either. The field applies to contains "=$A$2:$A$13".

[bebo021999]

zbor,
4°) Select a Rule Type: Use a formula to determine which cells to format, Format values where this formula is true: =$A2<>INDEX(test2,ROW($A2)) , Format: fill blue.
It didn't work either. The field applies to contains "=$A$2:$A$13".

Try to replace $A2(equals 2) with $A1(equals 1), because of test2 start from A2, so to get 1st value in test2, you must use $A1 in INDEX function.

[matiati]

Thanks bebo021999, that way it worked

The thread can be closed, regards!