conditional formating based on value in different worksheet

novicehacker

New Member
Joined
Mar 9, 2004
Messages
34
I want to use conditional formatting to turn a cell yellow. However the cell which i want to test is on another worksheet, and Excel gives me an error "You may not use references to other worksheets or other workboots for conditional formatting criteria" is there some way i can get around this?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Re: conditional formating based on value in different worksh

The only way round this (I know) is to use a named range as your reference.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
yes, GorD has it.

name your range on one sheet (xl 03 is best for this)
in the cell you want changed, CF, formula is:
=YourNamedRange<>""

or whatever check you wish to do. worked for me.

HTH
 

bjwillingham

Active Member
Joined
Jun 17, 2003
Messages
287
Re: conditional formating based on value in different worksh

It appears the other spreadsheet must be open for the format to work if using named ranges. However, if you have a 'cellx' (perhaps on a hidden tab) that directly references the speadsheet with the conditional format referencing that 'cellx', the format works even if the other spreadsheet is not opened.
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

Re: conditional formating based on value in different worksh

The exam question was related to another worksheet, not another workbook, but Ibelieve you are correct
 

bjwillingham

Active Member
Joined
Jun 17, 2003
Messages
287
Re: conditional formating based on value in different worksh

Oops! My bad. Three slaps on the wrist. :oops:

Well, maybe someone as confused as me will learn something.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Re: conditional formating based on value in different worksh

ya, me! i thought i was reading this all wrong! not enough coffee yet :devilish:

btw, i've donemuch worse... :unsure:
:LOL: :LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top