defining name issue in excel

associates · #1 (**permalink**) 04-26-07, 01:01

Hi,

I was wondering if anyone might be able to help me with my problem. I've got error when trying to define name for cells in the worksheet. I can define the name up to something like 65493 numbers of cells. Then, it just failed giving me error. Is this mean that it can only do up to 2 bytes?

I don't know what else i should try out. What my objective here is to be able to write the same data to two different worksheets called "modified data" and "original data" respectively. The original data worksheet is hidden from the end-user so they can only see the worksheet "modified data". What they can do with the "modified data" worksheet is they can edit or change the value in the cells. If the value in a cell in worksheet "modified data" is different from the value in the same cell in worksheet "original data", the colour of the cell will turn to red to notify user of the changes made by them. That's what i would like to see it happening.

Any ideas?

Thank you in advance

MikeTheBike · #2 (**permalink**) 04-26-07, 08:11

Hi

If I have interpreted you post correctly, then you have named 65,000 plus cells ?

If so, then there are better ways of spending your time !!??

I did have a thought though, If you are familiar with VBA (?), you could try pasting this into the "modified data" sheet module.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    
    For Each cel In Target
        If cel = Sheets("original data").Range(cel.Address) Then
            cel.Interior.ColorIndex = xlNone
        Else
            cel.Interior.ColorIndex = 36
        End If
    Next cel
End Sub

This assumes that the "original data" worksheet is named "original data" an is totally based on "the value in the same cell" indicated in you post.

The formatting of the cells would, of course, be your choice.

Is that any help !!??

MTB

Internet Services

Web Development

Digital Marketing

Consumer Tech