Need Help conditional formatting or macro across worksheets

[bejinsky01]

I have a somone complicated situation, wish i could talk to an expert on the phone. So here goes... I have 15 worksheets in one workbook, in column b in any worksheet any given number cannot appear more than 2 times, i have it setup in each worksheet, but i want to be in one worksheet and it search column b in all other worksheets and give a result.

Example:

sheet1 has the number 3 two times in column b, if i got to sheet2 column b and put in the number 3 i want it to turn red.

i want this to work no matter what sheet i'm in.

 

[VoG]

Sorry, answers by phone is not how the forum works.

I have removed your e-mail address to save you from being spammed. If somebody wants to contact you then they can send you a Personal Message. But the preferred and usual way of answering questions is to post a reply to the thread.

 

[bejinsky01]

Thx I'm new to this.

 

[VoG]

You should be able to use Conditional Formatting, maybe like this

Excel Workbook
	B
1	3
2	3
3
Sheet1

Excel Workbook
	B
1	1
2	2
3	3
4	4
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =COUNTIF(Sheet1!B:B,B1)>=2	Abc

 

[bejinsky01]

i tried that it says cant reference another worksheet for conditional formatting. so i think i need a macro, which i know nothing about, or maybe some sorta trick. thanks

 

[VoG]

Try creating a named range for your cells in column B of Sheet1 http://www.contextures.com/xlNames01.html

I named B1:B10 as MyRange. Then you can use this in your CF formula

Excel Workbook
	B
1	1
2	2
3	3
4	4
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	1. / Formula is =COUNTIF(MyRange,B1)>=2	Abc

P.S. or Excel 2010 allows references to other sheets in CF formulas.

 

[bejinsky01]

that worked for the most part, now how do i get it to work so that if i'm in sheet1 it will search all 14 other worksheets

 

[bejinsky01]

what i guess im saying is i can't have the number 3 appear more than 2 times in all 15 worksheets.

 

[VoG]

You will need VBA to do this.

I have several questions,

1 only colunn nmber 3
any sheet except sheet 1

Any sheet

and so on.

 

[bejinsky01]

i don't understand your questions so i will give an example the best way i can.

i have 15 worksheets, if i put the number 3 in column b on any sheet more than 2 times i want it to turn the cell solid red.

example: sheet1 & sheet 2 have number 3 in column b so when i go to sheet14 and try to put number 3 in column b that cell should turn red.

example: sheet4 has number 3 in column b two times, so when i go to say sheet7 and try to put number 3 in column b that cell should turn red.

i want this to work no matter what sheet i'm in.