Search and highlight cells

ghard1

Active Member
Joined
May 4, 2005
Messages
331
Hi everyone,

I have 3 worksheets A, B and C. Worksheet A and B have numbers in three columns. In worksheet C, column D, I would like a formula or conditional formatting which will search worksheet A for the number in column D and if it finds the same number as the one in the cell, it highlights the cell Green. I would also like it to search worksheet B and if the number is in worksheet B, highlight the cell Red. In no cases will the number be in both worksheets (A and B). Does this make sense? Is it possible?

Thanks for any help you can shed on this.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi and thank you for the quick reply. I have tried conditional formatting but I get an error stating that I cannot have references to other worksheets in conditional formatting. Am I missing something?
 
Upvote 0
Hi

You could use a helper cell on worksheet C, with a formula like this:
=IF(ISNA(MATCH(A1,A!A:A,0)),IF(ISNA(MATCH(A1,B!A:A,0)),"No","B"),"A")

and copied down. Then you can use your conditional formatting using the value from the helper cell.

Andrew
 
Upvote 0
Great idea and thanks for the tip. I'm struggling a bit with the if formula. I don't seem to be getting any "B" results when I should be. Only A's and No's.
 
Upvote 0
You can use CF across worksheets if you use a Named Range instead of Cell References

lenze
 
Upvote 0
Ok. So I've named the ranges, how can I get conditional formatting to work for me if the cell value is found within the named range?

Thanks.
 
Upvote 0
Named ranges is easier - I forgot about that!! :)

Assuming your named ranges are AA and BA (being sheet A column A and sheet B column A) then select the first cell in the range for which you want to set a conditional format (I have assumed this is cell A1, change to suit), then select menu option Format > Conditional Formatting > change 'cell value is' to 'formula is' and enter the following formula :

=NOT(ISNA(MATCH(A1,AA,0)))

and then pick a green coloured format. Before clicking Ok, add a 2nd condition by clicking 'Add', again change 'cell value is' to 'formula is' and enter the following formula for the 2nd condition:

=NOT(ISNA(MATCH(A1,BA,0)))

and then pick a the red coloured format > Ok.

Copy the cell you just set the format for, and then paste special > formats over the rest of the range.

Andrew
 
Upvote 0
Hi Andrew and Lenze thank you for all your help with this. It worked! I had one small issue and that is that I guess named ranges cannot be more than one column wide (or at least, the formula wasn't working when the range was say a1:c300.)

Thanks again.
 
Upvote 0
There shouldn't be a limit to the size of the Named Range. I believe its the MATCH that may be causing trouble. Give this a try
Code:
=COUNTIF(yourNamedRange,D1) >0

lenze
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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