Changing cell colour if calculated value in the cell changes based

user_15673892 0
Viewed 179

I have 2 worksheets in the same spreadsheet and 2nd worksheet dates are based on 1st worksheet with various vlookup. Now, I have known how to change the cell color if calculated value in the cell changes:

Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Interior.Color = 3
End Sub

When I use this VBA in the 2nd worksheet and change the 1st worksheet, the cell color in the 2nd worksheet will not change. I want cells with a formula in the 2nd worksheet to change their color when I manually update the 1st worksheet. Is it possible? Thanks!

enter image description here

enter image description here

excel-formula vba excel
1 Answers

I'll guess a solution: as your VBA code in 2nd Worksheet will have not effect, as we have not user triggering. So in your first Worksheet, we can do something like this:

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

When you change something in 1st worksheet, the second worksheet will change filling color.

The second solution more complex but closer to your requirement. It uses VBA Worksheet_Calculate() event in 2nd sheet, please remove all VBA from 1st worksheet first, like this:

Private Function getActualUsedRange(ByVal strVLookUp)
  Set getActualUsedRange = Range("A1").Resize(Cells.Find(what:=strVLookUp, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row, _
    Cells.Find(what:=strVLookUp, SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column)
End Function

'
' change color of all cells with formulas containing some string:
'
' strVLookUp: vlookup
' strFirstAddress: address of first cell, like $C$5
' objCell: range object of 1 cell
' objRange: range object of cells with formulas
' lBackColor: long for color index
'
Private Sub Worksheet_Calculate()

  Dim strVLookUp As String, strFirstAddress As String
  Dim objCell As Object, objRange As Object
  Dim lBackColor As Long

  ' search this string in formulas in 2nd Worksheet:
  strVLookUp = "vlookup"

  ' change back color as you want:
  lBackColor = RGB(255, 150, 0)

  ' get whole range with formulas involving "vlookup":
  Set objRange = getActualUsedRange(strVLookUp)
  
  ' if we find a range, loop over it:
  If Not objRange Is Nothing Then
  
    Set objCell = objRange.Find(strVLookUp, LookIn:=xlFormulas)
    
    If Not objCell Is Nothing Then
    
      strFirstAddress = objCell.Address
      
      Do
        objCell.Interior.Color = lBackColor
        Set objCell = objRange.FindNext(objCell)
      Loop While Not objCell Is Nothing And objCell.Address <> strFirstAddress
      
    End If

    ' free memory:
    Set objCell = Nothing
    Set objRange = Nothing
    '
  End If
'
End Sub

This is based on the following fact: in the 2nd Worksheet, some cells have formulas like this: =vlookup("MyValue", ...), you referenced the vlookup() function.

user_9530970
Related