Help fix my conditional format macro

[Milos]

Hi there,

I am just after a little help to finish my attempt of automating a conditional format that I use often. I use it to check to different columns of data to see if they are identical or not.

This is my code below.

Sub Conditional_Formatting_MatchData() Dim rng1, rng1a, rng2, rng2a As Range Dim DefaultRange1, DefaultRange2 As Range Dim FormatRuleInput As String 'RANGE1 Determine a default range based on user's Selection If TypeName(Selection) = "Range1" Then Set DefaultRange1 = Selection Else Set DefaultRange1 = ActiveCell End If 'RANGE1 Get A Cell Address From The User to Get Number Format From On Error Resume Next Set rng1 = Application.InputBox( _ Title:="Check range 1", _ Prompt:="Select a cell range to check against second range", _ Default:=DefaultRange1.Address, _ Type:=8) On Error GoTo 0 If rng1 Is Nothing Then Exit Sub 'Need to reference top of FIRST selection rng1a = Selection.Cells(1, 1).Select 'RANGE2 Determine a default range based on user's Selection If TypeName(Selection) = "Range2" Then Set DefaultRange2 = Selection Else Set DefaultRange2 = ActiveCell End If 'RANGE2 Get A Cell Address From The User to Get Number Format From On Error Resume Next Set rng2 = Application.InputBox( _ Title:="Check range 2", _ Prompt:="Select a cell range to check against second range", _ Default:=DefaultRange2.Address, _ Type:=8) On Error GoTo 0 If rng2 Is Nothing Then Exit Sub 'Need to reference top of SECOND selection rng2a = Selection.Cells(1, 1).Select 'FINAL define the rule for each conditional format With rng1 .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(AND(rng1a<>rng2a,rng2a<>""),AND(rng2a<>rng1a,rng1a<>""))" .FormatConditions(1).Interior.ColorIndex = 46 End With End Sub End With End Sub

Currently I have managed to get it somewhat there but the range values are just input as text in when the conditional formatting box is checked. I also do not know how to get the top cell referenced for two different selections.

WHAT I NEED: =OR(AND(A1<>B1,B1<>"),AND(B2<>B1,B1<>"))
WHAT I HAVE WITH CODE SO FAR: =OR(AND(rng1a<>rng2a,rng2a<>"),AND(rng2a<>rng1a,rng1a<>"))

Any help is appreciated.

Cheers,
Milos

 

[Norie]

Try this.

.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(AND(" & rng1a.Address(0,0) & "<>" & rng2a.Address(0,0) & "," & rng2a.Address(0,0) & "<>""""),AND(" & rng2a.Address(0,0)  & "<>" & rng1a.Address(0,0) & "," & rng1a.Address(0,0) & "<>""""))"

P.S. I think you could probably simplify the formula since you are doing the same check twice, i.e. checking that the values in 2 cells don't equal each other.

 

[Milos]

Thanks for that, yeah I will simplify it.

The data reference seems to not be working as I keep getting the object required error. Do know why that maybe?

 

[Norie]

Can you post the whole code with the change I suggested?

 

[Milos]

I have tried to simplify it a wee bit, just to get it to work. Still no go.


Sub Conditional_Formatting_MatchData() Dim rng1, rng1a, rng2, rng2a As Range Dim DefaultRange1, DefaultRange2 As Range Dim FormatRuleInput As String 'RANGE1 Determine a default range based on user's Selection If TypeName(Selection) = "Range1" Then Set DefaultRange1 = Selection Else Set DefaultRange1 = ActiveCell End If 'RANGE1 Get A Cell Address From The User to Get Number Format From On Error Resume Next Set rng1 = Application.InputBox( _ Title:="Check range 1", _ Prompt:="Select a cell range to check against second range", _ Default:=DefaultRange1.Address, _ Type:=8) On Error GoTo 0 If rng1 Is Nothing Then Exit Sub Set rng1a = Range("A1") 'RANGE2 Determine a default range based on user's Selection If TypeName(Selection) = "Range2" Then Set DefaultRange2 = Selection Else Set DefaultRange2 = ActiveCell End If 'RANGE2 Get A Cell Address From The User to Get Number Format From On Error Resume Next Set rng2 = Application.InputBox( _ Title:="Check range 2", _ Prompt:="Select a cell range to check against second range", _ Default:=DefaultRange2.Address, _ Type:=8) On Error GoTo 0 If rng2 Is Nothing Then Exit Sub Set rng2a = Range("B1") 'FINAL define the rule for each conditional format With rng1 .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(" & rng1a.Address(0, 0) & "<>" & rng2a.Address(0, 0) _ & "," & rng2a.Address(0, 0) & "<>""))" .FormatConditions(1).Interior.ColorIndex = 46 End With End Sub

 

[Norie]

What isn't working?

Are you getting errors or is the conditional formatting not working as expected?

 

[Milos]

It was getting a object error with the first formula weirdly even though it seemed correct. I revised the formula to the one below and it is working as needed.

.FormatConditions.Add Type:=xlExpression, Formula1:="=IF(" & rng1a.Address(0, 0) & "<>" & rng2a.Address(0, 0) & ",TRUE,FALSE)"

Thanks for your help Norie.