Changing cell colour if calculated value in the cell changes based

AxiaoNailo

New Member
Joined
Apr 17, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have 2 worksheets in the same spreadsheet and 2nd worksheet dates are based on 1st worksheet with various vlookups. Now, I have known how to change the cell color if the calculated value in the cell changes:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = RGB(255, 0, 0)
Worksheets("Name_Of_2nd_Worksheet").Range(Target.Address()).Interior.Color = RGB(255, 0, 0)
End Sub

but 2nd cell data are not in the same cells as the cells on the first worksheet. For example, When I modify A1 in worksheet 1, I use the vlookup function in worksheet 2 to track the data changes in cell C3 of A1. However, if I use the above code, it is still A1(in 2nd Worksheet)that changes color. What can I do to change the color of cell C3 in worksheet 2 when I modify cell A1?

In addition, because the data changes irregularly, I think I should not be able to use a conditional format. For example, the data will change from 7300 to 9600, or from 8000 to 6500. If I want to use a conditional format, the data change should need a change rule.

It is possible? Thanks!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,572
Office Version
  1. 2016
Platform
  1. Windows
If you are having the change event code in worksheet then when you change A1
1) Sheet1 range A1 will change to Red
2) SHeet2 range A1 will change to Red too

This is because Target refers to changed cell A1 of Sheet1. The target address in the line below is the Sheet1 target address:
Worksheets("Name_Of_2nd_Worksheet").Range(Target.Address()).Interior.Color = RGB(255, 0, 0)

If you replace the bold above with "C3" then Sheet2 range C3 will be Red.

Not cleat how your lookup looks like and not really understand what you are trying to do.
 

Forum statistics

Threads
1,141,310
Messages
5,705,677
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top