Change the color of a cell when it differs from a default value

[UncleBajubjubs]

Hello, I have a program used to estimate the cost of and then design large machinery. First, an estimator will enter the specifics needed for the machinery and select all the parts for its construction (motors, steel, etc.). This is all tallied up on a worksheet, "Selected Parts", and a cost is calculated so the customer can know if they want to buy it. If the customer accepts the price, then the engineers take over. They'll go through and check each piece to make sure that the machine will function, and if not, they will change out the piece for another piece that will work.

I'd like to add some formatting to highlight the cells in which the engineers change the part from the estimated value. For instance, if the estimator calls for a "50 horsepower motor" and then engineer changes it to a "60 horsepower motor", that cell is now highlighted in yellow.

I was initially looking at having a macro to copy the estimated values onto another worksheet, "Selected Parts Copy", and use conditional formatting to highlight the cells on "Selected Parts" whenever they differ from "Selected Parts Copy". However, the most complicated machines can have several hundred parts, and I'm hoping to find an easier way to do this so I do not have to manually set up conditional formatting on these hundreds of cells. Any ideas?

Thanks!

 

[steve the fish]

You can certainly compare two lists and see if the values in your new list appear in your old one? Is that maybe what you want? For example lets say your old components list was in column A and your new components list in column C. Use this CF formula:

=AND($C1<>"",COUNTIF($A:$A,$C1)=0)

and apply it to

=$C:$C

as an example.

 

[UncleBajubjubs]

You can certainly compare two lists and see if the values in your new list appear in your old one? Is that maybe what you want? For example lets say your old components list was in column A and your new components list in column C. Use this CF formula:

=AND($C1<>"",COUNTIF($A:$A,$C1)=0)

and apply it to

=$C:$C

as an example.

The worksheet is much more detailed than just a list though, as parts may be used multiple times and each cell corresponds with that parts placement.

So for example, there is a section "MOTOR 1" and several labeled cells under it, such as vendor, motor type, duty rating, etc. There can be over 20 motors, so say the engineer changes the duty rating of motor 10 from what the estimator put in, I want that cell specifically to be highlighted. I'm curious if there's a way to do this other than individually linking every cell to the copy of what the estimator put in.

 

[mumps]

Using "Selected Parts" and "Selected Parts Copy", you could use a Worksheet_Change macro in the "Selected Parts Copy" sheet to check if the changed cell is the same as the corresponding cell in the "Selected Parts" sheet. This of course assumes that "Selected Parts Copy" is a mirror copy of "Selected Parts". Would this work for you?

 

[UncleBajubjubs]

Using "Selected Parts" and "Selected Parts Copy", you could use a Worksheet_Change macro in the "Selected Parts Copy" sheet to check if the changed cell is the same as the corresponding cell in the "Selected Parts" sheet. This of course assumes that "Selected Parts Copy" is a mirror copy of "Selected Parts". Would this work for you?

I looked it up and that seems to be exactly what I need, but my execution seems to be faulty. I tried:


<code>

Private Sub Worksheet_Change(ByVal Target As Range)






ChangedCell = Target.Address


MsgBox ChangedCell 'just to se that it correctly grabs the cell


If ChangedCell.Value <> Worksheets("Selected Parts Copy").ChangedCell.Value Then' this line says requires object


ChangeCell.Pattern.xlPatternGray25
End If




End Sub

</code>

But this isn't working for me, any ideas?

 

[mumps]

Try this macro in the worksheet code module for "Selected Parts".

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target <> Sheets("Selected Parts Copy").Range(Target.Address).Value Then
        MsgBox ("Cell " & Target.Address(0, 0) & " has been changed.")
        Target.Interior.ColorIndex = 3
    Else
        Target.Interior.ColorIndex = xlNone
    End If
End Sub

 

[UncleBajubjubs]

Thank you, this works exactly as intended!

 

[mumps]

You are very welcome.

 

[UncleBajubjubs]

You are very welcome.

I encountered another issue I'm not sure how to fix. When I manually change something on the "Selected Parts" page, it correctly changes fill color. However, a lot of the cells will be filled in from calculations on other pages, and so when the data on other pages changes, the values on "Selected Parts" change as well. This does not trigger the color to be changed.
There's about 5 other sheets that link data to the "Selected Parts" sheet, so I'm not sure if I could just add code to each other those pages, or if I may need another solution.

 

[mumps]

A Worksheet_Change macro as I suggested is not triggered by a change in a cell that is the result of a formula. That would need a Worksheet_Calculate macro but in order to take care of that, I would need to know which cells change as a result of a formula. Would it be possible to upload a copy of your file to a free site such as www.box.com. or www.dropbox.com? Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.