Solved Can I use conditional formatting across separate workbooks?

May 11, 2017 at 07:45:41
Specs: Windows 7
If not, is there a way to use macros to accomplish this? I have Windows7 and Exel2010.

I have a master workbook with "Open Date" A1 and Expiration Date"B1 columns.

In separate workbooks I have it set up to link to the open dates, or flag it with an X if the column is empty. I also need the same cell in the secondary sheets to be formatted so that if the corresponding expiration date is expired it will turn the cell background red. I understand how to use the conditional formatting to do this if it is on the same sheet, but I can't can the conditional formatting to work across workbooks, and unfortunately I need to keep the 2 workbooks separate.


See More: Can I use conditional formatting across separate workbooks?

Report •

✔ Best Answer
May 13, 2017 at 20:10:12

Don't know if this will be of any help and
it may get a bit confusing......

This is what I did:

I have a workbook called "CF_arcoss_sheets.xlsm"
in which I used Define Names,
The Define Name is "MyColors" and refers to =Sheet3!$A$1:$A$4
Cell A1 has the text string red
Cell A2 the text string blue
Cell A3 the text string green
Cell A4 the text string yellow

In Column E I have the text strings:
Cell E1 the red hen
Cell E2 the orange hen
Cell E3 the blue hen
Cell E4 the blue hen
Cell E5 the silver hen
Cell E6 the green hen
Cell E7 the white hen
Cell E8 the black hen

I used the Conditional Formatting Rule of:

=OR(ISNUMBER(SEARCH(MyColors, E1)))

And applied it to Cells E1:E8

And selected the color Green to be applied.

So cells E1 E3 E4 E6, now turn Green.

That takes care of the First Workbook.

I next created Book1.xlsm, this was a new, blank workbook.

Next I went back to the CF_arcoss_sheets.xlsm
and selected Cells E1:E8
Selected Copy

Then went to the new Book1.xlsm and pasted them into Column E of Sheet 1

Now, in Book1.xlsm the Define Name of "MyColors" was transferred,
with the Refers to as:

=[CF_arcoss_sheets.xlsm]Sheet3!$A$1:$A$4

Book1.xlsm now uses the references to CF_arcoss_sheets.xlsm and
the cells E1 E3 E4 E6, turn Green.

Problem is, the moment I close CF_across_sheets.xlsm, the reference
is broken and the colors in Book1.xlsm go away.
So BOTH workbooks need to be open for one to access the other.

MIKE

http://www.skeptic.com/



#1
May 11, 2017 at 08:54:40
Why can't you just use the same Rule for each workbook?

You could try using a Defined Name, I know this will work across WorkSheets,
but have never tried it across WorkBooks.

You might also try the Format Painter and/or Copy/Paste/Formats.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
May 11, 2017 at 19:43:25
Thanks for the response, but that won't work. I've already tried the defined name and it won't work across separate workbooks. I need the format to change automatically based on the master workbook. The values are being linked in for the open date, but not the expiration date on the secondary workbooks. I need to keep those values separate.

Report •

#3
May 11, 2017 at 20:20:08
re: " I need the format to change automatically based on the master workbook."

I think what Mike was suggesting was that you use the same rules in both workbooks and then let the cells in each workbook trigger the formatting.

e.g. Set up this rule in both workbooks:

(Cell references simplified for clarity)

If A1 > 100, turn the cell Green

When Master!A1 is >100, Master!A1 will turn Green

Since (we are assuming) Secondary!A1 contains =Master!A1, Secondary!A1 will turn Green when Master!A1 turns Green because it too will be >100.

If that won't work, then you'll need to tell us why, i.e. is it not as simple as (something like) "Secondary!A1 contains =Master!A1"?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
May 12, 2017 at 02:03:32
Yes,

1. You can use the format painter

2. You can use Copy, Edit, Paste Special, Formats

You should tell us the conditional formatting you are using because it may make a difference


Report •

#5
May 13, 2017 at 18:50:36
Ok...I have Master A1, Master A2 and Secondary A1.

Secondary A1 is set up =IF(MasterA1=0,"X",MasterA1)

I also need it to be set somehow that if MasterA2<TODAY() it turns the Secondary cell RED. When I set Master A1 to turn red from MasterA2s date and then paste the format into the secondary workbook it turns red, however if I change the date it won't auto change the color which is what I need it to do. Unless I'm using the wrong paste format function? I feel like i have to be missing something really silly here.

For Reference sake Master A1 and A2 are in 1 workbook and Secondary A1 is in a separate workbook.

message edited by George113


Report •

#6
May 13, 2017 at 20:10:12
✔ Best Answer

Don't know if this will be of any help and
it may get a bit confusing......

This is what I did:

I have a workbook called "CF_arcoss_sheets.xlsm"
in which I used Define Names,
The Define Name is "MyColors" and refers to =Sheet3!$A$1:$A$4
Cell A1 has the text string red
Cell A2 the text string blue
Cell A3 the text string green
Cell A4 the text string yellow

In Column E I have the text strings:
Cell E1 the red hen
Cell E2 the orange hen
Cell E3 the blue hen
Cell E4 the blue hen
Cell E5 the silver hen
Cell E6 the green hen
Cell E7 the white hen
Cell E8 the black hen

I used the Conditional Formatting Rule of:

=OR(ISNUMBER(SEARCH(MyColors, E1)))

And applied it to Cells E1:E8

And selected the color Green to be applied.

So cells E1 E3 E4 E6, now turn Green.

That takes care of the First Workbook.

I next created Book1.xlsm, this was a new, blank workbook.

Next I went back to the CF_arcoss_sheets.xlsm
and selected Cells E1:E8
Selected Copy

Then went to the new Book1.xlsm and pasted them into Column E of Sheet 1

Now, in Book1.xlsm the Define Name of "MyColors" was transferred,
with the Refers to as:

=[CF_arcoss_sheets.xlsm]Sheet3!$A$1:$A$4

Book1.xlsm now uses the references to CF_arcoss_sheets.xlsm and
the cells E1 E3 E4 E6, turn Green.

Problem is, the moment I close CF_across_sheets.xlsm, the reference
is broken and the colors in Book1.xlsm go away.
So BOTH workbooks need to be open for one to access the other.

MIKE

http://www.skeptic.com/


Report •

Ask Question