Changing cell colour if calculated value in the cell changes

[strongman86]

Hi,
Is it possible to change cell colour if calculated value in the cell changes. Lets say I have 2 worksheets in the same spreadsheet. 1st worksheet contains data entered manually.
2nd worksheet contains cells with formulas using data from 1st worksheet. I want cells with formula in 2nd worksheet change it's colour when I manually update 1st worksheet. Thanks.

 

[Special-K99]

If a cell in the 2nd worksheet is based on the 1st worksheet then when the cell changes in the 1st worksheet the cell in the 2nd worksheet will change as well.
You may need VBA for this.

 

[bhos123]

you can simply use conditional formatting. Home>> Conditional formatting in Styles. then select the rule that applies to your criteria.

 

[strongman86]

I will attach my dummy spreadsheet. I have narrowed it down to 1 row. I'm looking to highlight cells I3 , J3 and S3 on Sheet 3 when their value changes after I manually change Sheet 1 cell A2. Sheet 2 also has data but never changed. Ultimately I want to be able to highlight multiple cells in Columns I, J & S when more rows are added.
Thanks.

https://drive.google.com/open?id=0BwIM2moJgfSwWkJqam95aUNSZWs

 

[Nixhex11]

Highlight I3 on sheet 3, Conditional Formatting/New Rule/Use a formula to determine which cells to format/in the input field, type in: =I3<>43 / then click format and choose which fill color you'd like it to change to. Repeat with the J3 and S3 using the formulas =J3<>62 and =S3<>1.34 respectively.

 

[strongman86]

Thanks Nixhex11,
It works OK for one off, but I will have couple 100 rows with different data and this solution won't work for me. Is there different way of doing this? Thanks.

 

[Nixhex11]

The great thing about conditional formatting is that you can use the format painter to apply it to lots of other cells. What I'd do is copy all the cells you want to behave this way, paste their values somewhere else maintaining the spatial relationship between all the cells, then apply the conditional formatting in the same way as above but instead of the cell <> the value in itself, it will <> the corresponding cells you've pasted elsewhere (making sure in the formula there are no absolute references). Then you can use the format painter to paint all the other cells for your desired effect.

 

[strongman86]

Thanks for your reply. Much appreciated. I'm not following you. Would it be possible for you to edit my attached V2 spreadsheet with added rows and send it back to me please?
I do very similar spreadsheet for work, but much bigger around 300 rows. The way I'm currently do is Open Sheet 1 & 3 side by side, go through Sheet 1 column A updating values and at the same time watching Sheet 3 and if any of column I, J or S values changing I highlight them in colour manually and once gone through all column A, I export and use all highlighted values outside excel. As you can see not very efficient at all.

https://drive.google.com/open?id=0BwIM2moJgfSwVEJWbFhjdnNmOU0

 

[Nixhex11]

Here is a step by step explanation:
-Copy Column I in Sheet 3 - in your example it's I3:I20.
-Paste Special: Paste Values in Column W (it can be anywhere you want but we'll use Column W starting in row 3 so now I3 and W3 are the same values, I4 and W4 are the same, etc.)
-Click on cell I3, open up conditional formating, choose New Rule, then choose "Use a formula to determine which cells to format" under Select a Rule Type.
-In the blank field where it says "Format values where this formula is true:", type in the formula: I3 <> W3
-Click format, under Fill option, choose the color you want your cells to change, click ok, then click ok twice again to set it.
-Now try changing A2 in Sheet 1, it should automatically change I3's fill color.
-Now that you're set with that, highlight cell I3 in sheet 3 and click on format painter. I3 will now have a dashed outline rotating around it clockwise and your cursor is now a + sign with a brush next to it. Now, highlight the rest of the cells in column I to apply the same formatting to each of these cells.
-Repeat this process for columns J and S.

One caveat, you must keep the values that you pasted in the other columns or this won't work. If you don't like them being there, you can either hide the columns or color their font white.

 

[strongman86]

That should do it. Thanks a lot.