Ex07 in '03 Format - Change format if information changes

I need code to either change the formatting or to set a value in a cell if 
data in that row has been changed. It may work best to set a value in a 
column that is locked when the worksheet is protected and then is 
reset/removed after the updates have been identified.

Here are the details if it helps:
I distribute a workbook that is maintained by outside sources. The workbook 
contains two tabs/sheets, Location and Technician.

The Location worksheet contains mostly information which is maintained by me 
and is protected/locked when the workbook is distributed. This worksheet also 
contains columns that are validated data using a list identified by a dynamic 
named range on the second worksheet. These columns are not locked when the 
worksheet is protected. When a change occurs on this sheet I would like for 
it to be visually obvious, either by a value in a new column, or by the 
formatting of the changed text (e.g. bold text).

The Technician worksheet is maintained by the outside sources. It contains 
names and addresses of resources used and assigned to the Locations on the 
first worksheet. When a change occurs on this sheet I would like for it to be 
visually obvious, either by a value in a new column, or by the formatting of 
the changed text (e.g. bold text).

Only one column is locked on the Technician worksheet. It is a formula that 
creates a unique ID based on the input of some of the information in the 
other columns.

On both sheets, I use conditional formatting to provide alternating 
background colors to make the information more ledgible:
=AND(MOD(SUBTOTAL(3,$A2:$A$2),2)=0,$A2<>"")

Location also has Conditional formatting in eight columns which applies Gray 
background to columns that should not be populated. This condition is based 
on a value entered entered in another column (1 through 8).


-- 
TraciAnn
0
TraciAnn (18)
4/20/2009 7:05:04 PM
excel 39879 articles. 2 followers. Follow

2 Replies
381 Views

Similar Articles

[PageSpeed] 6

Hi TraciAnn,

See if the following will help.

Assume that the worksheet in question is called "My Sheet"
Insert a new worksheet to store all the values. Call it "Master Copy"
Select all the cells in "My Sheet" and then Copy.
Change worksheets to "Master Copy"
Select Cell A1 and then Paste Special -> Values.
Protect and hide worksheet "Master Copy".

You can then use the following code to identify the cells that have been 
changed.

Sub TestForChanges()

Dim wsMysheet As Worksheet
Dim wsMastCopy As Worksheet
Dim c As Range

Set wsMysheet = Sheets("My Sheet")
Set wsMastCopy = Sheets("Master Copy")

With wsMysheet
    For Each c In .UsedRange
        If c.Value <> wsMastCopy.Range(c.Address) Then
            c.Font.Bold = True  'See Other options below
        End If
    Next c
End With

End Sub

'Other options to highlight changes

'Color the background
c.Interior.ColorIndex = 3  'Red

'Color the font
c.Font.Color = vbRed

-- 
Regards,

OssieMac


"TraciAnn" wrote:

> I need code to either change the formatting or to set a value in a cell if 
> data in that row has been changed. It may work best to set a value in a 
> column that is locked when the worksheet is protected and then is 
> reset/removed after the updates have been identified.
> 
> Here are the details if it helps:
> I distribute a workbook that is maintained by outside sources. The workbook 
> contains two tabs/sheets, Location and Technician.
> 
> The Location worksheet contains mostly information which is maintained by me 
> and is protected/locked when the workbook is distributed. This worksheet also 
> contains columns that are validated data using a list identified by a dynamic 
> named range on the second worksheet. These columns are not locked when the 
> worksheet is protected. When a change occurs on this sheet I would like for 
> it to be visually obvious, either by a value in a new column, or by the 
> formatting of the changed text (e.g. bold text).
> 
> The Technician worksheet is maintained by the outside sources. It contains 
> names and addresses of resources used and assigned to the Locations on the 
> first worksheet. When a change occurs on this sheet I would like for it to be 
> visually obvious, either by a value in a new column, or by the formatting of 
> the changed text (e.g. bold text).
> 
> Only one column is locked on the Technician worksheet. It is a formula that 
> creates a unique ID based on the input of some of the information in the 
> other columns.
> 
> On both sheets, I use conditional formatting to provide alternating 
> background colors to make the information more ledgible:
> =AND(MOD(SUBTOTAL(3,$A2:$A$2),2)=0,$A2<>"")
> 
> Location also has Conditional formatting in eight columns which applies Gray 
> background to columns that should not be populated. This condition is based 
> on a value entered entered in another column (1 through 8).
> 
> 
> -- 
> TraciAnn
0
OssieMac (238)
4/21/2009 12:26:02 PM
Ossie, 

Yes, this works. However, I didn't think this through completely. Your code 
works but I don't know how to keep the data synchronized between changes.

I'm going to reword my questions in another post with a more descriptive 
subject.

Please look for it and tell me if you can expand on your answer any.

Thanks so much for your help!!!

-- 
TraciAnn


"OssieMac" wrote:

> Hi TraciAnn,
> 
> See if the following will help.
> 
> Assume that the worksheet in question is called "My Sheet"
> Insert a new worksheet to store all the values. Call it "Master Copy"
> Select all the cells in "My Sheet" and then Copy.
> Change worksheets to "Master Copy"
> Select Cell A1 and then Paste Special -> Values.
> Protect and hide worksheet "Master Copy".
> 
> You can then use the following code to identify the cells that have been 
> changed.
> 
> Sub TestForChanges()
> 
> Dim wsMysheet As Worksheet
> Dim wsMastCopy As Worksheet
> Dim c As Range
> 
> Set wsMysheet = Sheets("My Sheet")
> Set wsMastCopy = Sheets("Master Copy")
> 
> With wsMysheet
>     For Each c In .UsedRange
>         If c.Value <> wsMastCopy.Range(c.Address) Then
>             c.Font.Bold = True  'See Other options below
>         End If
>     Next c
> End With
> 
> End Sub
> 
> 'Other options to highlight changes
> 
> 'Color the background
> c.Interior.ColorIndex = 3  'Red
> 
> 'Color the font
> c.Font.Color = vbRed
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
> "TraciAnn" wrote:
> 
> > I need code to either change the formatting or to set a value in a cell if 
> > data in that row has been changed. It may work best to set a value in a 
> > column that is locked when the worksheet is protected and then is 
> > reset/removed after the updates have been identified.
> > 
> > Here are the details if it helps:
> > I distribute a workbook that is maintained by outside sources. The workbook 
> > contains two tabs/sheets, Location and Technician.
> > 
> > The Location worksheet contains mostly information which is maintained by me 
> > and is protected/locked when the workbook is distributed. This worksheet also 
> > contains columns that are validated data using a list identified by a dynamic 
> > named range on the second worksheet. These columns are not locked when the 
> > worksheet is protected. When a change occurs on this sheet I would like for 
> > it to be visually obvious, either by a value in a new column, or by the 
> > formatting of the changed text (e.g. bold text).
> > 
> > The Technician worksheet is maintained by the outside sources. It contains 
> > names and addresses of resources used and assigned to the Locations on the 
> > first worksheet. When a change occurs on this sheet I would like for it to be 
> > visually obvious, either by a value in a new column, or by the formatting of 
> > the changed text (e.g. bold text).
> > 
> > Only one column is locked on the Technician worksheet. It is a formula that 
> > creates a unique ID based on the input of some of the information in the 
> > other columns.
> > 
> > On both sheets, I use conditional formatting to provide alternating 
> > background colors to make the information more ledgible:
> > =AND(MOD(SUBTOTAL(3,$A2:$A$2),2)=0,$A2<>"")
> > 
> > Location also has Conditional formatting in eight columns which applies Gray 
> > background to columns that should not be populated. This condition is based 
> > on a value entered entered in another column (1 through 8).
> > 
> > 
> > -- 
> > TraciAnn
0
TraciAnn (18)
4/24/2009 12:49:01 PM
Reply:

Similar Artilces:

SRS Report not displaying timesheet in correct format
Hope someone can help on this issue: This report creates a matrix of timesheet information for all resources under a single Timesheet Manager. The number of columns is determined by the number of open timesheet periods, and the order of periods is intended to be descending by start date, but that's the problem. Although the query returns data in the correct order, the SRS report matrix does not. Our open periods are 2/15/10 to 3/22/10, and the query returns them in correctly: Period Name Work Start Date End Date Project Name Resource Name 2010 Week 12 39.25 3/22/2010 3/28...

Conditional formating of Charts
Hi, I have a scatter chart and need to conditionally format points and associated labels for each point. The condition is not related to the data series to which the chart is linked. Let me know if there is any specific VBA code for doing the same. You can reach me at krishna.guha@citigroup.com There are some non-VBA suggestions here: http://peltiertech.com/Excel/Charts/ConditionalChart1.html To change some points of a series or some series in a chart based on a known criterion, you can adapt one of the macros in this post: http://www.google.com/groups?selm=3F4B8D49.3080508%40yah...

How to replicate Excels' numeric "Accounting" format in Access Rep
How to replicate Excels' numeric "Accounting" format in Access Report client want's Access report to output values in format identical to Excel's numeric "Accounting" format if I try to difine it as such it converts the 'Format' definition to >> \acc"ou"n"ti"n\g and strangely dosen't give the desired result I'm using Office 2003 on Windows 2000 -- Jim Have you tried Currency with 2 decimal places? -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom...

my file in Word '03 was converted. need 2 convert from mswrd632
my file in Word '03 was converted. need 2 convert from mswrd632. Need help? I cannot get my original file because of mswrd632, whatever that means. Go to http://support.microsoft.com/kb/973904/en-us and press the FixIt button. -- Terry Farrell - MSWord MVP "A problem with mswrd632" <A problem with mswrd632@discussions.microsoft.com> wrote in message news:FB9A32FC-6E7E-46C3-8256-77DC6001C3B7@microsoft.com... > my file in Word '03 was converted. need 2 convert from mswrd632. Need > help? > I cannot get my original file because of mswrd632...

Subsequent pages having top/center formatting.
Hi, I have a simple question. I have a large listing of information across cells that takes up multiple pages. However, whenever I want to print, I find on the last page that I have the last cells on my project centered directly in the middle of a page. Is there any way for me to change it so that it will be positioned towards the top of the page ala my first couple pages? Thank you very much for any and all help. Tytus wrote: > Hi, I have a simple question. I have a large listing of information across > cells that takes up multiple pages. However, whenever I want to print, ...

Conditional formatting: adding extra conditions
Hi, The Conditional Format in Excel only allows you to enter 3 conditions, therefore ending in 3 different formats available only. Is there a way to increase this to 6 or more conditions? Thanks a lot Pierre Hi Pierre! Apart from conditional formats you do have ability to control formats for negatives, positives, zero and text. But generally you'd go for VBA to apply more than three formats. -- Regards Norman Harker MVP (Excel) Sydney, Australia Holidays and Observances Friday 18th July: Mexico (Day of Mourning death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day...

Ranges within Conditional Formatting
Within a cell, thisis basically what I want to do but am having trouble: if the value is between 95-100 background = green else if value is between (90-95) or between (100-105) background = yellow else if value is <90 OR >105) background = red Any ideas? Ryno wrote: > Within a cell, thisis basically what I want to do but am having > trouble: if the value is between 95-100 background = green > else if value is between (90-95) or between (100-105) background > = yellow else if value is <90 OR >105) background = red > > Any ideas? If you...

Ex07 Hell
2 HT servers in different AD sites, both. When they try to send to each other, the queue manager gives this message. Both EX07, I can ping and telnet on 25 to each from each and create messages from with SMTP commands. Delivery to each other is not working. Delivery Type: SMTP Relay to Remote Active Directory Site Primary Target IP address responded with "451 5.7.3 Cannot achieve Exchange Server authentication." On Mar 30, 3:37 pm, ryanlsand...@gmail.com wrote: > 2 HT servers in different AD sites, both. When they try to send to > each other, the queue manager gives thi...

Incoming email formats
Hi there, I've got a small problem. I have several rules in my outlook to process incoming emails. My incoming emails are in HTML format. I've noticed when I get the emails, the outlook rule doesn't work. The email sits in Inbox. Since in my rules I am looking for certain words, it seems like Outlook is not smart enough to scan the body of the message. If I send myself a test email in ASCII from my gmail account and place those certain words in the body of the message, outlook process that incoming emails. Also if I edit the incoming email messages and save them and re-run m...

Format for text field
Hi- I have a text field, the format of this field is ##.## (ex: 09.10) How can I check this field if it have a value then it must be in a format like above? (null is OK) Thank for any replpy. >>How can I check this field if it have a value then it must be in a format like above? (null is OK) Null is not a value. Are you wanting to check for a numerical value or that the text is formated? -- Build a little, test a little. "MN" wrote: > Hi- I have a text field, the format of this field is ##.## (ex: 09.10) > How can I check this field if it have a va...

excel help 03-08-10
I need help to write this function. In my table I have one column A1:A100, in each row it could have one text, could be "B" or "P", I want to compare from A1 down to A100 if "B" is more than "P" by one then stop the comparation and display a "W" in next column at the row. Thanks Hung Is this what you want: =if(countif(A1:A100,"B")>countif(A1:A100,"P"),"W","") ? Regards, Fred "Hung" <Hung@discussions.microsoft.com> wrote in message news:2F1921A4-4729-4D4A-8521-4...

Deleting conditional formatting
I am unable to delete the conditional formatting on a range of cells probably due to some cutting/pasteing. The conditional format in greyed out in format menu. Any help is greatly appreciated. You probably have sheet protection turned on. http://www.mvps.org/dmcritchie/excel/grayedout.htm --- 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 "okiebound" <okiebound@hotmail.com> wrote in message news:008601c47a23$a...

e-mail 03-03-08
I'm having trouble sending slides in my e-mail. where can I get instructions? On Mon, 3 Mar 2008 15:45:21 -0500, "vera" <momshere334@verizon.net> wrote: >I'm having trouble sending slides in my e-mail. where can I get instructions? Well, not in a Microsoft Access(TM) database forum, which is where you're asking. Please scroll down the list of subject areas and post in a newsgroup supporting your EMail software. -- John W. Vinson [MVP] ...

formatting series
hi am lost here i want to format my pie chart data series but i can't seem to get it how is it done on excel 2003 Try right-clicking* on the pie chart > Format data series *point your cursor anywhere inside the pie Then tinker with the settings say in, the Data Labels tab -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "Amin" wrote: > hi am lost here i want to format my pie chart data series but i can't seem to > get it how is it done on excel 2003 ...

Outlook 03
Everything worked except I did not get my email accounts, and that is ok with me but I am having problems with: 1) How do I rid of one of the Outlook Today 2) I cannot get my contacts to show when I click to TO when emailing. I see it in my address book. This is the same thing that happened in my Outlook 02 and I cannot remember what stuff you guys told me to do, please help. Did I hurt my Outlook 2003 by copying those files if I did what should I do to make it work the correct way. Thank you so much in helping me if you can I did a clean install of Outlook 03 I copied from Outlook 02 em...

Prep for Ex07 to join Ex03 in Child Domain
AD Root domain is place holder only, All Ex03 is in a child domain. Are these the correct steps to prepare AD for Ex07? /PrepareLegacyExchangePermissions: ExchangeDomain (FQDN) /PrepareSchema has to be run from root domain /PrepareAD run this from the child Exchange Domain /PrepareDomain: Exchange Domain (FQDN) /PrepareLegacyExchangePermissions Enterprise Admins /PrepareSchema Enterprise Admins + Schema Admins Same site and same domain as the Schema Master DC /PrepareAD Enterprise Admins /PrepareDomain FQDN Domain Admins /PrepareAllDomains Enterprise Admi...

How do I apply Conditional Formatting to a range?
I can apply Conditional Formatting to a single cell, e.g. E13, but how do I apply the same CF to a range of cells, e.g. B13:E15, i.e. the CF of all cells in the range is dependent on the condition being met in a single specified cell in the range? Select the whole range and apply the Conditional Formatting formula all at once. If you always want to use A1 somewhere in your formula, make sure you enter that address using $a$1. Just like a regular formula that's copied elsewhere, cells with absolute addresses won't change. Chris wrote: > > I can apply Conditional Formatting t...

Missing Tools Menu Item
Home Use Program installed Office Pro 2003 and is missing the Tools menu item to restore deleted items. Anyone experience this or know what to do? This option only exists when you are connected to an Exchange server -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Pam B." <anonymous@discussions.microsoft.com> wrote in message news:037201c49137$67bb6660$a401280a@phx.gbl... > Home Use Program installed Office Pro 2003 and is missing > the To...

Conditional Formatting
I want to color a cell based on the value of that cell being greater than zero and another cell not being equal to "x". I've used conditional formatting with "formula is" and formula - IF(A1>0,B1<>"X") This does not seem to work - what am I doing wrong?????? Use AND for multiple criteria to be satisfied, like this .. Assume you want to apply CF to col A Select col A, apply CF using Formula is: =AND(A1>0,B1<>"X") Format as desired>OK out Success? hit the YES below -- Max Singapore xde --- "Pat999"...

displaying international format phone numbers
OL 2002 SP3 Win XP HE Follow-up to: microsoft.public.outlook.contacts Hi, I have a folder with hundreds of contacts. All telephone numbers display as +12129999999 I would like to have them all display as +1 (212) 999-9999 It does automatically change to that if I manually open the contact and click on the little pencil icon to the right of the phone number field. However, I can't really do this manually for hundred's of contacts. How can I automate this? Thanks a lot. ...

windows update error message 03-03-10
Hi For the last week or so I've been getting the message "The website has encountered a problem and cannot display the page you are trying to view. The options provided below might help you solve the problem. " I have 4 computers on a router using the firewall in the router, no other firewalls running and using AVG version 9.0 for all 4 PCs, which are all running XP SP3. The message is displayed from all the computers. Nothing was changed-- no settings on the firewall or on the PCs. Thanks! You receive a "Windows Update has encountered an error and ca...

Formatting
I am working in Microcsoft Publisher.I have typed in text and then inserted a picture. How do I get the text to wrap around the picture. When working in Word I could just insert a picture and the text could be wrapped around it. In publisher the text is confined to a text box. Whatever I seem to do the picture remains the same only the size of the text box alters and text will not wrap aound the picture.I have tried using edit points but have got in a real mudle I am desperate now. Any help please? What version Publisher? Are you sending the image to the front? Click the dog icon, (picture t...

Outlook '03 start error
Please help I am getting a "fatal execution engine error (0x7927baca) cordbg.exe 1a0xc68 AND cannot find JIT denugger when trying to repair. Thank you in advance for your help. http://www.dotnet247.com/247reference/msgs/20/104190.aspx -- Michael Henderson midahe@goduke.nc.rr.com Remove 'goduke', if not spam "sean" <sjbr28@ameritech.net> wrote in message news:086701c3b819$828ce1f0$a401280a@phx.gbl... > Please help > I am getting a "fatal execution engine error (0x7927baca) > cordbg.exe 1a0xc68 AND cannot find JIT denugger when > tryi...

love 03-03-08
Dear eugene I love you from eugene "eugene lin" <eugeneintheale@yahoo.co.uk> wrote > Dear eugene > I love you > from eugene And now thousands and thousands of people around the world know that you love yourself, Eugene. Sorry to see that the caps key on your computer is not working too well. Larry Linson Microsoft Office Access MVP ...

cell format, custom type: mmm-yy; working with
A cell is referenced in an equation. That cells format contains a custom type: mmm-yy. How does a vba function work with that generally? Is it a string to be chopped up and worked with, or can you use some object property to get at the value or month and year? Cate, If you want to use the formatted value of the cell, you need to use the ..Text property of the range object For example: Sub Test() Dim myC As Range Set myC = ActiveCell With myC .Value = Now MsgBox CDbl(.Value) & " is the cell's underlying number value" .NumberFormat = "mmm-yy" ...