Using conditional format and referencing another worksheet

bailbonds

Board Regular
Joined
Mar 19, 2010
Messages
70
Hi,

I use a worksheet with data in it and I want to use conditional formatting to highlight a cell if there is data in another worksheet within the same workbook.

So, for example, Sheet A, column c is fully populated down to row 500. If I enter text in Sheet B, cell B1 I want Sheet A, cell C1 to have shading applied.

I know this is possible as I did have it but someone deleted it from my sheet.

Thanks in advance
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Apologies, but I'm not following this. I used the link but it seems to be suggesting matches. The cell I want to reference to will not have the same data as the cell I want to format. Both cells have text and if cell B1 in Sheet B is blank then cell C1 in Sheet A will have no format. However, if cell B2 in Sheet B has text then cell C2 in Sheet A has a colour.

I am using Excel 2007 if this helps.
 
Upvote 0
Cheers but I get the message 'You cannot use references to other worksheets or workbooks for conditional formatting crieteria.'

The annoying thing is I recently had this until it got wiped and I recall using the "formula" criteria in Formatting.
 
Upvote 0
Sorry, I'm getting confused. That would work in 2010. In earlier versions you need to use named ranges. So if you named Sheet2!B2 as Fred you could use

=Fred<>""
 
Upvote 0
With 2007 and earlier, you need to assign a name to Sheet2!B2
e.g.
Name: Sheet2_B2, or whatever you want
Refers to:
Code:
=Sheet2!$B$2

Then you can refer to this from anywhere in the workbook
e.g.
Sheet1 Cell A1
Conditional Formatting > Formula is
Code:
=Sheet2_B2=5
Forrmat "Yellow"
 
Upvote 0
That highlights all cells. I've sort of got around it by using an if statement in Sheet A worksheet which makes another cell "1" if there is text in a specific cell in Sheet B. Then I can reference that. It seems to work.

Thanks for your help thoough.
 
Upvote 0
That highlights all cells
... only if you selected all the cells and applied the C/F to them!

A word of warning 2010 can reference different sheets without using names, but I know several large companies still using 2003 with 2007 compatibility.
Probably safer to use names in 2010 for a few years yet!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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
Back
Top