Macro to find and match debit/Credits by BOLD

[Posky]

Hi All,

I have a macro that matches debits and credits in a column and then makes it bold, I was wondering if I can tweak this code to also highlight it as well as bold

Sub ModifyFindMatch()

Dim myVar
Dim x As Integer
Dim y As Integer
Dim mycell

ActiveSheet.Range("u23").Select
y = ActiveSheet.Range("u23").End(xlDown).Row - ActiveCell.Row
Do Until IsEmpty(ActiveCell)
myVar = ActiveCell
For i = 1 To y
If ActiveCell.Offset(i).Value = -1 * myVar Then
Set mycell = ActiveCell.Offset(i)
If mycell.Font.FontStyle <> "Bold" Then
mycell.Font.FontStyle = "Bold"
ActiveCell.Font.FontStyle = "Bold"
Exit For
End If
End If
Next i
ActiveCell.Offset(1).Select
Loop

End Sub

 

[Posky]

Also, to add to that code, I was wondering if there was a way where it can look at the whole column as I have to keep changing range cell start,, in the above code for example u23,

Thanks for any help..

 

[Villalobos]

Hi,

Highlight just by color?

 

[Posky]

yes highlight the cell any colour once it matches-- it works at the moment by just bold

Thanks

 

[Villalobos]

try this:

Sub ModifyFindMatch()
Dim myVar
Dim x As Integer
Dim y As Integer
Dim mycell

ActiveSheet.Range("u1").Select
 y = ActiveSheet.Range("u1").End(xlDown).Row - ActiveCell.Row
 
Do Until IsEmpty(ActiveCell)
 myVar = ActiveCell
  For i = 1 To y
  If ActiveCell.Offset(i).Value = -1 * myVar Then
  Set mycell = ActiveCell.Offset(i)
  If mycell.Font.FontStyle <> "Bold" Then
  mycell.Font.FontStyle = "Bold"
  mycell.Interior.Color = 65321
  ActiveCell.Font.FontStyle = "Bold"
  ActiveCell.Interior.Color = 65321
  Exit For
  End If
  End If
  Next i
 ActiveCell.Offset(1).Select
Loop

End Sub

 

[Posky]

Brilliant, it works, only I have to keep changing the start cell, can i get it look at the whole column

 

[Posky]

As at the moment it looks from cell U1 and stops at a blank cell which is fine, but then down the column i need it look again without changing from U1 to U56 again--- any ideas?

 

[Villalobos]

Ok, could you test this conditional formatting:

Sub Test()
  Columns("U:U").Select
  Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  "=IF(ISERROR(IF(U1=VLOOKUP(-1*U1,$U:$U,1,FALSE),,)),FALSE,TRUE)"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Bold = True
  .Italic = False
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 65321
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
End Sub

if the language of your PC is not English then you have to translate this formula:

 =IF(ISERROR(IF(U1=VLOOKUP(-1*U1,$U:$U,1,FALSE),,)),FALSE,TRUE)

 

[Posky]

Wow, yes it works!!! Brilliant... what about if you want the blank cell and want to run the macro to start after every blank cell- I dont mind running after every blank cell but I dont want to change the cell number...

 

[Posky]

I have attached a file so you can see example