Work around Conditional Formatting

Dear all,

    There are 2 worksheets (Sheet1, Sheet2) containing similar data in my 
workbook. I want to underline the text in a cell in Sheet2 if the text in 
that cell is the same as the cell in the same place in Sheet1. (For example, 
if Sheet1!A1 = Sheet2!A1, then Sheet2!A1 is underlined.) Can I write a 
user-defined function to check whether the contents in two cells are equal? 
Thanks in advance.

Best Regards,
Andy 


0
1/1/2006 1:55:23 PM
excel 39879 articles. 2 followers. Follow

3 Replies
374 Views

Similar Articles

[PageSpeed] 42

Chips has lots of stuff on duplicates, see www.cpearson.com

-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andy Chan" <chankhandy-msnewsgroup@yahoo.com.hk> wrote in message 
news:43b7df4c$1@127.0.0.1...
> Dear all,
>
>    There are 2 worksheets (Sheet1, Sheet2) containing similar data in my 
> workbook. I want to underline the text in a cell in Sheet2 if the text in 
> that cell is the same as the cell in the same place in Sheet1. (For 
> example, if Sheet1!A1 = Sheet2!A1, then Sheet2!A1 is underlined.) Can I 
> write a user-defined function to check whether the contents in two cells 
> are equal? Thanks in advance.
>
> Best Regards,
> Andy
> 


0
bliengme5824 (3040)
1/1/2006 2:06:52 PM
Oops - his name is Chip (singular)!
Goodbye Mr Chips!
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
news:eAYSbytDGHA.3832@TK2MSFTNGP10.phx.gbl...
> Chips has lots of stuff on duplicates, see www.cpearson.com
>
> -- 
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Andy Chan" <chankhandy-msnewsgroup@yahoo.com.hk> wrote in message 
> news:43b7df4c$1@127.0.0.1...
>> Dear all,
>>
>>    There are 2 worksheets (Sheet1, Sheet2) containing similar data in my 
>> workbook. I want to underline the text in a cell in Sheet2 if the text in 
>> that cell is the same as the cell in the same place in Sheet1. (For 
>> example, if Sheet1!A1 = Sheet2!A1, then Sheet2!A1 is underlined.) Can I 
>> write a user-defined function to check whether the contents in two cells 
>> are equal? Thanks in advance.
>>
>> Best Regards,
>> Andy
>>
>
> 


0
bliengme5824 (3040)
1/1/2006 2:12:06 PM
Hi Andy,
Please include the complete question in the text body,  one should
not be expected to look at the subject to understand the question
once the question is actually being viewed.

Conditional Formatting unfortunately cannot refer to another sheet,
but you can use a defined name for a reference and use that.
Select  Cell A1 on Sheet2  then place   sht2 into the name box.
as a shortcut to using    Insert, Name, Define, ...

Then select the area of interest on Sheet1  with cell A1 as the active cell.
Format, Conditional Formatting,
    Condition 1 Formula is:    =A1=OFFSET(sht2,ROW()-1,COLUMN()-1)

You might want to use a pattern color instead of using  Font, underscore
to try to underscore a empty cell.

---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Andy Chan" <chankhandy-msnewsgroup@yahoo.com.hk> wrote in message news:43b7df4c$1@127.0.0.1...
> Dear all,
>
>     There are 2 worksheets (Sheet1, Sheet2) containing similar data in my
> workbook. I want to underline the text in a cell in Sheet2 if the text in
> that cell is the same as the cell in the same place in Sheet1. (For example,
> if Sheet1!A1 = Sheet2!A1, then Sheet2!A1 is underlined.) Can I write a
> user-defined function to check whether the contents in two cells are equal?
> Thanks in advance.
>
> Best Regards,
> Andy
>
>


0
1/2/2006 5:18:25 PM
Reply:

Similar Artilces:

I need a logoff script (with conditions and cancellation dialog box)
Platform: Windows XP SP3, domain workstation. I need a script that: 1. Checks the username of the logged on user. - If the user is one of two specific users, it logs them off immediately. - If the user is not one of two specific users, proceed to step 2. 2. Display a dialog box with a timer ticking down the seconds from 20 minutes. Also in the dialog box, show the caption, "Unless the countdown is cancelled, the user will be automatically logged off." At the bottom of the dialog box, show a button that reads "Cancel Logoff". - If the button is clicked, the ...

series of conditional formulas #2
hello, i would like to create a formula which says: if the value in column A is -1, and the value in column B is 2, and th value is column C is 0, then return the value -1 AND if the value in column A is 2, and the value in column B is 1, and th value is column C is -1, then return the value 0 AND etc.etc. There is a finite list of possible permutations for the values i columns A, B, and C, and I would like to define a result for eac possible permutation. I know that I could accomplish this by dedicatin a separate column and IF() formula to each possible outcome, the summing the results, but...

Excel cell formatting
Sometimes a cell in a worksheet won't allow me to format. If I select "format cells" by right-clicking or from the "Format" menu nothing happens, even though I can edit the format of an adjacent cell. This happens in many different worksheets. Any ideas about why this happens? ...

Date format for printing checks in various locations
Hi, I was wondering whether the date format used when printing checks is customisable? Specifically, we need to be able to print check in mm-dd-yyyy format for our American office as well a in dd-mm-yyyy for our Australian and English offices. Our basic configuration is Win 2000 SS2000 for the backend. We use Citrix for our US & UK offices to login to the great plains databases here in Australia. Any help would be appreciated Cheers michaelm@towersoft.com.au You could set up a calculated field that looks at the global company id field to determine the date format. The calculated...

Rules work but have a delay
I have a rule which whenever an email come into the Inbox, if the person is in my address box to move the email to another folder. The rule works ok but sometimes an email will come into the Inbox and sit there for 30 seconds to 2 or 3 minutes before it moves to the other folder. Clicking and Send/Receive has no effect. Anyone know how to get rid of this delay????? Thanks in advance For those that might be able to help... What Version? What operating system? Up to date? "Copper" <noreplay@aol.com> wrote in message news:uSP8pdAaHHA.1508@TK2MSFTNGP06.phx.gbl... >I ha...

modeless dialog in dll doesnt work ...
Hai all, i have an issue with the CWnd derived class Control Which is Owner draw ,that is used as a editcontrol with multiline functionality. The CWnd devied class has a parent which is a CDialog . When i create the Dialog as Modal the framework calls the pretanslateMessage() of the CWnd Derived Class and CDialog . But if create as modeless it doesnt called . I dont know the reason why... if any body can suggest some idea or came across this issue kinldy give me suggestions .... I had passed the handle of the parent window m_pMainWnd of CWinApp from client to the dll in Create(IDD,m_p...

COUNTIF using multiple conditions?
Hello folks - would appreciate help with this, please! I'm setting up a summary sheet to help track housing for a conference. For each date I have a formula to track the number of rooms, i.e. for January 7: =COUNTIF('Housing List'!F:F,"1/07/2005")+B4-(COUNTIF('Housing List'!G:G,"1/07/2005")) This counts the number of people arriving on the 7th, plus the number people already arrived prior, minus anyone leaving on the 7th. So now I need to break it down further, and use that same formula but only count the single rooms, so the ones with "Single&qu...

Copy entire rows matching condition to a new sheet
Hi I want to copy entire rows matching a condition to a new sheet. The original sheet contains for example a column called impact with values between 1 and 20. Is there a possibility to automatically copy all rows with an impact > 16 to a new sheet called Priority 1, all with a value between 12 and 16 to a sheet priority 2, etc instead of copying the sheet manually, sort the data manually and delete those rows not meeting the criterium? Thanks very much This could be done by using the data>filter>autofilter. Record a macro while doing it so you can modify to do it again. -...

Exchange Notifications not working :-( help
Hello all, Ok here is my new problem. Exchange Notifications not working. I’m running Exchange 2003 enterprise Cluster I have setup monitoring of the C drive free space threshold, SMTP Queues growth and some other default monitoring exchange stuff. I then went to the notifications of exchange system manager and setup email notifications. I know one of my drives on exchange exceeded its size and passed the threshold in the monitoring tab, the other day the SMTP queues growths were exceeded also. I setup my notification to send an alert to an email address and on the warning sta...

Open form to specific record quits working
I have a customer search form that opens a specific customer order. Sometimes when I add a field or make a minor change to the customer order form, the customer search pulls a blank or new record. I delete the change I made to the order form but a blank record is still pulled. What can I do to fix this? Thanks Sorry, I meant to post this to Access. "Ron Weaver" wrote: > I have a customer search form that opens a specific customer order. Sometimes > when I add a field or make a minor change to the customer order form, the > customer search pulls a blank or new record...

upload bank transactions coda format
I am searching how to upload my bank transactions (Belgium). We get a file from our bank in coda format. Anyone who can tip me? PVBE, We have add on to the Bank Reconciliation Module. it is an electronic reconcile module. We support BAI code format files and other txt files. If you are interested you can email me gbuenafe@gpcsystems.com and we'll see if we could create a new import functionality for the CODA format. Gerald "pvbe" wrote: > I am searching how to upload my bank transactions (Belgium). We get a file > from our bank in coda format. > Anyone who can...

data from many work books
How do I collect data from many workbooks from select columns into a single workbook automatically. I don't want to open each one manually and copy and paste from it to the main workbook. The system should ask me for which file to be opened. Please can you help. Thanks and regards, Sandeep S. This can be done with VBA code (a macro). You haven't given enough information to provide a more detailed answer, but there is one command available that does allow you to browse and identify files to be worked with that will probably end up in the code: Application.GetO...

Excel XP question, working with Array's
Hello Guys/Girls, I have the following problem. I have one main-list (Row A and B) with all member numbers. Also I got a list (Row K and L) which contains a second List with financial information and a part of the numbers of the members-list. I would like to combine this. Is there een option, like the if-function, which could check the mainlist with the second-list and if there has been found a match, the financial information put in Colum C behind the right member number?. Greetings from the Netherlands, Johan Heegsma Hi Johan could you clarify why columns A & B contain member num...

Can I Rotate with a Condition -or- Can I Display Graphics with a Formula
I am trying to write a function that will display less than and greater than signs, but do so in two directions; I'd like them to go left to right or up and down. So my questions is, since I can't find a char() for a reverse caret, is it possible to either rotate the caret with a conditional, or is there a way to display graphic characters (which I'll draw) using an Excel formula? Thanks Here's a goopd example of how you can use Excel to rotate an object, but I'm not sure how to use this concept to reverse a caret... http://www.andypope.info/charts/3drotate.htm ...

Custom Number Formats
Does anyone know of a good resource for looking up the capabilities and limitations of Custom Number formats? I'm trying to recreate a convoluted Word format inside of Excel. So far I've been unsuccessful. tj How about "About custom number formats" in XL Help? In article <153F3092-E5EB-489D-B568-A63C7BA41130@microsoft.com>, "tjtjjtjt" <anonymous@discussions.microsoft.com> wrote: > Does anyone know of a good resource for looking up the capabilities and > limitations of Custom Number formats? > I'm trying to recreate a convoluted Word fo...

Export in cvs format
Hi I already do export some reports en queries in to an ..xls/rtf/tst/pdf-format. But my webmaster want it exported in cvs format. Is this possible? -- Luc Ringoir Belgium Am 06.06.2010 09:09, schrieb Luciano: > Hi > I already do export some reports en queries in to an > .xls/rtf/tst/pdf-format. But my webmaster want it exported in cvs format. Is > this possible? Hi there, thats possible. When you´re about to start a manual export you only have to choose the right format and setup an export specification. The you can use this all the time. Regards,...

Conditional Formatting OR Expression??
I am using all 3 conditions so is there a way to combine these 2 lines of code into 1 condition? They both result in turning the cell red: '(this line looks to see if there is a percent other than 0 or 100 and calculates the percent where we should be versus the actual, cell H33) =IF(AND(H33>0%,H33<100%),(H33<=((H32-$C$2)/$H$8))) OR =IF(H33=0%,(H32-$C$2)<$H$8) '(this line looks to see if there are still enough days to complete) Cell H33 is a % Comp, H32 is End Date, $C$2 is Today's Date and $H$8 is # days to complete, just FYI. THANKS!! Stacey Do an OR =OR(cond1...

Modify general format to date format.
When importing from payroll, it gives me all dates formatted as general numbers (i.e. 07/17/2009 is presented as 7172009. Is there any way to convert these numbers into dates? If your Windows Regional Settings recognise a format of mddyyyy, you can apply the below formula and format the formula cell to date.. =--TEXT(A1,"00\-00\-0000") -- Jacob (MVP - Excel) "SherryS" wrote: > When importing from payroll, it gives me all dates formatted as general > numbers (i.e. 07/17/2009 is presented as 7172009. Is there any way to > convert the...

can't format for leading zeros
i'm importing data from excel. i need to have the data with 10 digits, so leading zeros must be added to some of the numbers, but not all. i have my table set as text, and the format is set as 0000000000. yet, when i save, the table doesn't convert to add the leading zeros. am i supposed to be writing a query to do this? or should i be changing the formatting in a different way any help is much appreciated Hi format the cells as number and apply your format. This should do the trick -- Regards Frank Kabel Frankfurt, Germany "iris b" <anonymous@discussions.microsoft...

Transfer data in an other text box field on a condition
Hi folks, I have a field on my report that show me a sum of "cartons to received" for each truck appointed. now I want to transfer that sum of "cartons to received" into another text box if the cartons are received. Anyone have an idea, how can I do this? If you need additional details, let me know. Thanking you in advance for your support Fred's ...

Forwarding HTML Formatted Message
Message 'disappears' when I attempt to forward an HTML formatted message. ...

Blocked formats
Using Office 2007 on Windows XP. I tried to open a doc file and got the warning: This error occurs if you try to open a Microsoft Office document and the file type for that document has been blocked by a registry policy setting. To help secure your computer, Microsoft or the administrator of this computer implemented a registry policy setting that prevents opening this type of file. I followed the yellow-brick road to 922849 which says: Locate and then click the following registry subkey: HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Word\Security\FileOpenBlock ...

TLS not working for some senders (inbound)
We have been using TLS (over SMTP) for many years on Exchange 2003 SP2 (Windows 2003 SP2). A time has come to renew a certificate as the existing one was expiring, so we did renew. We installed the new ceritificate successfully 2 month before expiration of the current/old certificate. When the old certificate expired we were still receiving secure email (TLS) from most senders but few. No problems to send secure email. We tested the SMTP protocol using telnet and all looked fine up to the last step you can test with telnet (issuing STARTTLS command and receive correct response ...

Borders Around Groups
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I've been grouping images and captions in a book, but now I can't seem to figure out how to do a border that will surround the group -- the same border surrounding both the image and the caption. When I select the group, then select a line in the formatting palette, borders appear separately around the image and the caption, but I want it to surround the group I've created. Thanks for any help. Word can only put borders around "paragraphs". It has no concept of what you call a "...

CRM 4
We had a CRM 4 setup which was working successfully: - Server 1 is a Windows 2003 Server with CRM Server installed - Server 2 is a Windows 2003 Server with SQL 2005, Reporting Services with CRM Data connector installed The SQL 2005 server was upgraded to SQL 2008 Enterprise. After the installation and rebooting, the CRM Server was working apart from Reports. REPORT ERROR MESSAGES ====================== When a report is run from a CLIENT machine, the following error appears - "The request failed with HTTP status 401: Unauthorized." [WebException: The request failed with HTTP status...