VBA to replace previous table and check if one cells value is equal to the previous o

[baxius]

Hello,

I couldn't figure it out on my own so i decided to turn to the professionals.

I have a Macro in which if a value is entered in a cell right of the table, the table values ,formulas,format from above is copied right below the old one. Now the thing i need is that one of the copied cells would check if it is the same value from the previous table and create pop up: New value (or at least change it's colour). To put it simply i attached a workbook and the whole code. IF a value is entered in G column. The previous cells are copied and pasted nearby. What is need is when a new table is created, to check if (For this instance) B24=B35 and if not create a Message box or at least change it's colour. Thank you for any tips or help.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

Set r = Target.Cells(1, 1)

If Len(r.Value) = 0 Then Exit Sub

If r.Column <> 7 Then Exit Sub
If r.Row Mod 11 <> 1 Then Exit Sub

r.Offset(-11, -6).Resize(11, 5).Copy r.Offset(0, -6)
End Sub

[Paul_Hossler]

The previous cells are copied and pasted nearby. What is need is when a new table is created, to check if (For this instance) B24=B35 and if not create a Message box or at least change it's colour. Thank you for any tips or help.

If the block of cells is copied, why would the NOT be the same (at that point)

[baxius]

Because later another value will be entered in adjacent cell which almost always will be different. The copied block also copies formulas. The main cell with the formula looks that the entered value in an adjacent cell and brings a unique value, which may be different in another block of cells.

[Paul_Hossler]

In the WS code module

In the test attachment WB, I put some formulas in to the copy area that point to col M to make sure that changes were picked up

Conditional Formatting compares the 'copied' to the 'copy' including cells with formulas




Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, rSrc As Range, rDest As Range
Dim sDest As String

Set r = Target.Cells(1, 1)

If Len(r.Value) = 0 Then Exit Sub

If r.Column <> 7 Then Exit Sub
If r.Row Mod 11 <> 1 Then Exit Sub


Set rSrc = r.Offset(-11, -6).Resize(11, 5)
Set rDest = r.Offset(0, -6).Resize(11, 5)
sDest = rDest.Cells(1, 1).Address(False, False)


rSrc.Copy rDest

With rDest
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=" & sDest & "<>OFFSET(" & sDest & ",-11,0)"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).Interior.Color = vbRed
.FormatConditions(1).StopIfTrue = False
End With


End Sub

[baxius]

Tested the attachment. Run time error 5. Invalid procedure call or argument in
.FormatConditions.Add Type:=xlExpression, Formula1:="=" & sDest & "<>OFFSET(" & sDest & ",-11,0)"

[Paul_Hossler]

Hmmm -- works for me

25569

What version of Excel are you using?

Put a breakpoint on that line and go to the Immediate Window and see what

"=" & sDest & "<>OFFSET(" & sDest & ",-11,0)"

is

[baxius]

Figured it out. Thank you for your work and time that you putted in.