Altering conditional formatting macro to work across different worksheets

[Milos]

Hi there,

I have a conditional formatting macro that works well comparing lists of non-matching data on the same worksheet. What it does fail to do is to also compare data lists on two different worksheets. Is there a way to adjust the code so that it handles selecting data from 2 different worksheets?

Code is posted below.

Sub Cond_Format_Data_Match()

Dim rng1, rng2 As Range

'RANGE1 Input a range to check against second list
  On Error Resume Next
    Set rng1 = Application.InputBox( _
      Title:="Select List 1", _
      prompt:="Select part of a column", Type:=8)
  Err.Clear
  On Error GoTo 0

  If rng1 Is Nothing Then Exit Sub
    
'RANGE2 Input a range to check against first list
  On Error Resume Next
    Set rng2 = Application.InputBox( _
      Title:="Select List 2", _
      prompt:="Select part of a column", Type:=8)
  Err.Clear
  On Error GoTo 0

  If rng2 Is Nothing Then Exit Sub

'Set the conditional format formula
With rng1
    .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(" & rng1.Address(0, 0) & "<>" & rng2.Address(0, 0) & ",TRUE,FALSE)"
    .FormatConditions(1).Interior.Color = RGB(226, 239, 218)
End With

With rng2
    .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(" & rng2.Address(0, 0) & "<>" & rng1.Address(0, 0) & ",TRUE,FALSE)"
    .FormatConditions(1).Interior.Color = RGB(226, 239, 218)
End With

End Sub

Thanks in advance,
Milos

 

[StephenCrump]

With rng1
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & rng1.Address(0, 0) & " <> " & rng2.Address(0, 0, , 1)
'.....
With rng2
    .FormatConditions.Add Type:=xlExpression, Formula1:="=" & rng2.Address(0, 0) & " <> " & rng1.Address(0, 0, , 1)

 

[Milos]

Thanks Stephen! You have saved me a lot of copy/pasting!

 

[StephenCrump]

You have saved me a lot of copy/pasting!

Good to hear, thanks.

 

[Milos]

Stephen do you know if there is an easy way to apply this to work across different workbooks also?

Happy to start another thread if this is too off topic..

e.g. rng1 from Sheet1 WorkbookA
rng2 from Sheet1 WorkbookB

Cheers,
Milos

 

[StephenCrump]

You can't directly conditionally format between two workbooks.

You can work around this if you want by linking wbk1 to wbk2, and conditionally formatting between wbk1 and the link copy of wbk2.

That's not something I'd want to do, but I hate links generally.

 

[Milos]

Not worth the effort i think. Thanks for the advice Stephen.

Cheers,
Milos