How to create "duplicate checking" from Conditional Formatting in VBA?

[Eloise T]

I have six, inefficiently written no doubt, but working segments in this VBA that have been removed for space consideration (10,000 word limit).

The VBA segment notated as "Column B" needs to be adjusted so it will Conditionally Format B7:B257 to find duplicates. (see picture below)

I have tried several times (at least 5) to Record Macro of selecting the "Format only unique or duplicate values" but only get:
Sub Macro1()
.
.
End Sub

Sub Macro2()
.
.
End Sub

etc.

What is the VBA code necessary to find duplicates?

More importantly, what is the secret to get Macros to Record more than Sub Macro1() .... End Sub?

Thank you.

Sub Conditional_Format_Reset()
  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
  
  If ws.Cells(Rows.Count, 3).End(xlUp).Row > 6 Then
'  (Rows.Count, 3) tells ...?
  
'Column B ------------------------------------------------------------------------------------------

  With ws.Range("B7:B257")
  
  .FormatConditions.Delete
NEED FORMULA HERE  .FormatConditions.Add Type:=xlExpression, Formula1:="=?????????????????????,$B7)))"
  .FormatConditions(.FormatConditions.Count).Font.Bold = True  'Bold font
  .FormatConditions(.FormatConditions.Count).Font.ColorIndex = 3  'Red font

  .FormatConditions(.FormatConditions.Count).Interior.Pattern = xlPatternLinearGradient

  .FormatConditions(.FormatConditions.Count).Interior.Gradient.Degree = 0
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Clear
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Add (0)
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Add(0).Color = RGB(255, 153, 255)
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Add(0).TintAndShade = 0
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Add(0).Color = RGB(255, 153, 255)
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Add(0).TintAndShade = 0.25
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Add(0).Color = RGB(255, 153, 255)
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Add(0).TintAndShade = 0.5
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Add(0).Color = RGB(255, 153, 255)
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Add(0).TintAndShade = 0.75
  .FormatConditions(.FormatConditions.Count).Interior.Gradient.ColorStops.Add(1).Color = RGB(255, 153, 255)
  
  .FormatConditions(.FormatConditions.Count).StopIfTrue = False

  End With
  
  
'Column O ------------------------------------------------------------------------------------------
' Sq.rt. chr ASCII 8730

  With ws.Range("O7:O257")
  
  .FormatConditions.Delete
  .FormatConditions.Add Type:=xlExpression, Formula1:="=($O7=""CheckMark"")*($C7>$L7)"
  .FormatConditions(.FormatConditions.Count).Font.Bold = True  'Bold font
  .FormatConditions(.FormatConditions.Count).Font.ColorIndex = 3  'Red font
'  .FormatConditions(.FormatConditions.Count).Interior.ColorIndex = 2  'White background (white assumed)
  .FormatConditions(.FormatConditions.Count).StopIfTrue = False

'  End With
 

'Column O ------------------------------------------------------------------------------------------
' Sq.rt.chr. &H221A

'  With ws.Range("O7:O257")
'  .FormatConditions.Delete

  .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($O7=""CheckMark"",$C7<$L7)"
  .FormatConditions(.FormatConditions.Count).Font.Bold = True  'Bold font
  .FormatConditions(.FormatConditions.Count).Font.ColorIndex = 3  'Red font
'  .FormatConditions(.FormatConditions.Count).Interior.ColorIndex = 2  'White background (white assumed)
  .FormatConditions(.FormatConditions.Count).StopIfTrue = False

  End With

  End If  'Concludes: If ws.Cells(Rows.Count, 3).End(xlUp).Row > 2 Then

  Next  'Concludes: For Each ws In ThisWorkbook.Worksheets


'THREE BEEPS----------------------------------------------------------------------------------------
  Beep
'  Pause a second before engaging the next Beep so they don't run together and sound like only one Beep.
'  hrs:mi:secs
  Application.Wait Now + TimeValue("0:00:01")
  Beep
'  Pause a second before engaging the next Beep so they don't run together and sound like only one Beep.
  Application.Wait Now + TimeValue("0:00:01")
  Beep

End Sub

 

[shrivallabha]

If I record this in Excel 2016, I get code as in attached file.

See if it helps.

 

[Eloise T]

It not only helped, it worked! Thank you!
These 3 lines made the difference from your recorded macro:

.FormatConditions.AddUniqueValues
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(.FormatConditions.Count).DupeUnique = xlDuplicate

 

[Eloise T]

If I record this in Excel 2016, I get code as in attached file.

See if it helps.

What is the secret to get Macros to Record more than Sub Macro1() .... End Sub?

 

[shrivallabha]

- I just followed the standard procedure for recording macro. That was the reason why I included excel version in the response.

- If I remember correctly then you have Excel 2007 and Excel 2016.

You should do testing in both versions and see if it is version issue!

 

[Eloise T]

They took 2007 away (almost a month ago) and "permanently" replaced it with 2016. I miss 2007...it NEVER misbehaved or choked!

I'm thinking there must be some setting that needs to be made...other than turning on Developer tab.

I use two machines. I'll try to record a macro on the other, less used one and report back.

 

[shrivallabha]

I had done some testing and posted results in one of your previous threads.
https://chandoo.org/forum/threads/conditional-formatting-using-vba-continued.35565/#post-213113

If you are using it for recording new rule then it should work in my opinion since you've confirmed version to be 2016 (it could be working in previous versions as well but I have not checked). It doesn't work if you do modification task as much as I remember.

I do not think the Developer Ribbon has got anything to do with it even remotely. I turned the developer ribbon off and recorded using the macro recorder button at the bottom of the pane and it records!

 

[Eloise T]

I went through a part of your posted testing

https://chandoo.org/forum/threads/conditional-formatting-using-vba-continued.35565/#post-213113

...and it created macro code...The problem is I have no idea what I did to make it work that time...The only difference was a had more than one cell highlighted A1-J20. ...and I not even sure that was it. I'm going to try again with just a single cell.