Hi

I was wondering if anyone knew if it was possible to get Exel to
automatically flag cells that use a specific cell in their formula, but only
when the value of the specific cell has been changed.

I have a speadsheet which is updated regularly, however, the same cells do
not always need to be updated. The cells that are updated initially are in
the first half of the spreadsheet. These cells are then used in the formulas
of cells in the second half of the spreadsheet. However, changes in value
also need to be made to the cells in the second half, but only if changes in
the first half have occurred. I was hoping that instead of having to go
through and marking each cell that needs to be updated one at a time, exel
would be able to flag up the cells which contain the initial cells in the
formula which were already updated and then it would be just a case of
plugging in values. It would save so much time, not to mention my eyesight.

Hope this makes sense.

Any help would be very much appreciated.

Thanks

Hoc

Hi,

I can't think of a way of actually searching for the cell address in the
formulas because the search has to be set to find in part of the formula and
therfore a search for say address A2 will find A2 but it will also find
A21,A22 etc to A29.

However, another method which might help is:-
1. Prior to working on your worksheet, select all cells in the worksheet and
Copy and Paste Special Values to another worksheet.

2. Make the necessary changes to your worksheet.

3. Run a macro to identify all cells in the used area of your worksheet
where the values do not equal the values in the copy and set the background
color to say yellow. (I prefer yellow because it is still easy to read the
print).

If you want assistance with the macro then let me know and at the same time
provide me with answers to the following:-

1. Do you need instructions for copying the macro into your workbook and
getting it up and running?

2. If the answer is Yes to above then what version of Excel are you using?

If you do want further help, it will probably be another 18 hrs or so before
I can do anything on it because where I am about to go to bed and then I have
some other things to do in the morning.


Regards,

OssieMac

Hi again,

I decided to do something on this now and provide you with the macro and if
you are able to copy it in and run it without further help then OK but if you
want further instructions the get back to me and I will assist.

Note the comments in the macro. You will have to edit the sheet names to
match the names you are using.


Sub Find_Changes()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim cel As Range
Dim strAddress As String

'Edit next line to match the worksheet
'on which you make the changes
Set ws1 = Sheets("Sheet1")

'Edit next line to match the worksheet
'with the pasted values
Set ws2 = Sheets("Sheet2")

With ws1
Set rng1 = ws1.UsedRange
End With

For Each cel In rng1
strAddress = cel.Address
If cel.Value < ws2.Range(strAddress) Then
cel.Interior.ColorIndex = 6
End If
Next cel

End Sub

Regards,

OssieMac



"OssieMac" wrote:

Hi,

I can't think of a way of actually searching for the cell address in the
formulas because the search has to be set to find in part of the formula and
therfore a search for say address A2 will find A2 but it will also find
A21,A22 etc to A29.

However, another method which might help is:-
1. Prior to working on your worksheet, select all cells in the worksheet and
Copy and Paste Special Values to another worksheet.

2. Make the necessary changes to your worksheet.

3. Run a macro to identify all cells in the used area of your worksheet
where the values do not equal the values in the copy and set the background
color to say yellow. (I prefer yellow because it is still easy to read the
print).

If you want assistance with the macro then let me know and at the same time
provide me with answers to the following:-

1. Do you need instructions for copying the macro into your workbook and
getting it up and running?

2. If the answer is Yes to above then what version of Excel are you using?

If you do want further help, it will probably be another 18 hrs or so before
I can do anything on it because where I am about to go to bed and then I have
some other things to do in the morning.


Regards,

OssieMac

Hi OssieMac,

Thanks so much for your help, it worked. You are a genius!

This is actually the first time I have used any kind of group/forum and to
be honest I did not think that I would get an answer, how wrong I was.

I really appreciate you taking the time to help.

Thanks very much.

Hoc

"OssieMac" wrote:

Hi again,

I decided to do something on this now and provide you with the macro and if
you are able to copy it in and run it without further help then OK but if you
want further instructions the get back to me and I will assist.

Note the comments in the macro. You will have to edit the sheet names to
match the names you are using.


Sub Find_Changes()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim cel As Range
Dim strAddress As String

'Edit next line to match the worksheet
'on which you make the changes
Set ws1 = Sheets("Sheet1")

'Edit next line to match the worksheet
'with the pasted values
Set ws2 = Sheets("Sheet2")

With ws1
Set rng1 = ws1.UsedRange
End With

For Each cel In rng1
strAddress = cel.Address
If cel.Value < ws2.Range(strAddress) Then
cel.Interior.ColorIndex = 6
End If
Next cel

End Sub

Regards,

OssieMac



"OssieMac" wrote:

Hi,

I can't think of a way of actually searching for the cell address in the
formulas because the search has to be set to find in part of the formula and
therfore a search for say address A2 will find A2 but it will also find
A21,A22 etc to A29.

However, another method which might help is:-
1. Prior to working on your worksheet, select all cells in the worksheet and
Copy and Paste Special Values to another worksheet.

2. Make the necessary changes to your worksheet.

3. Run a macro to identify all cells in the used area of your worksheet
where the values do not equal the values in the copy and set the background
color to say yellow. (I prefer yellow because it is still easy to read the
print).

If you want assistance with the macro then let me know and at the same time
provide me with answers to the following:-

1. Do you need instructions for copying the macro into your workbook and
getting it up and running?

2. If the answer is Yes to above then what version of Excel are you using?

If you do want further help, it will probably be another 18 hrs or so before
I can do anything on it because where I am about to go to bed and then I have
some other things to do in the morning.


Regards,

OssieMac