VBA to Compare/Highlight Formatting Changes

[David20]

I'm trying to design a template that highlights changes made so the QC reviewer doesn't have to manually compare the template data and the new/modified data cell by cell (it could potentially be hundreds of cells with paragraphs, bulleted lists, etc., in each cell). The original data is on worksheet 'Template', the changes on 'Working'. I made a button/macro that copies everything from 'Template' to 'Working' and then applies Conditional Formatting to 'Working' to highlight cells where changes to the content are made. It works just as intended. (If it matters, the range used can vary quite a bit and since I'm not very skilled with VBA I just applied the Conditional Formatting to the entire 'Working' sheet.)

My problem is sometimes the change is a formatting change, not a content change. Specifically, if a portion of the original text needs to be deleted but nothing added, the deleted text is shown by strike-through and red text. Conditional Formatting doesn't recognize this as a change since it's just formatting, not content. I can't change the use of strike-through and red, it's company SOP.

Is there a VBA function to compare formatting changes and highlight the cell like Conditional Formatting does for a content change?

 

[Jeffrey Mahoney]

You could use a UDF to test of strikethrough has been applied. Add this to a standard module and use it in a cell formula:
=IsStrikeThrough(L3)

Function IsStrikeThrough(R As Range) As Boolean
  If R.Font.Strikethrough = True Then IsStrikeThrough = True
End Function

 

[David20]

Thank you. That gave me a partial solution and may be my answer for an unrelated problem. If the entire cell is formatted as strikethrough, your solution works. However, that will almost never be the case for my workbook. It will almost always be a few words or maybe a sentence within the cell (leaving all the rest of the text standard/unformatted). Is there a way to adjust the "If" code to check for "IfContainsStrikeThrough" as opposed to "if the entire cell is strikethrough"?

 

[Jeffrey Mahoney]

This function is going to take a considerable more time to process lots of cells. Use sparingly.

Function IsStrikeThrough(R As Range) As Boolean
  Dim Rng As Range
  
  Set Rng = R.Areas(1).Resize(1, 1)
  If Rng.Font.Strikethrough = True Then
    IsStrikeThrough = True
    Exit Function
  Else
  
    Dim i As Long
    With Rng
      For i = 1 To .Characters.Count
        If .Characters(i, 1).Font.Strikethrough Then
          IsStrikeThrough = True
          Exit For
        End If
      Next i
    End With
    
  End If
End Function

 

[David20]

Thank you for your help! I won't be able to fully test this for a couple few days, but I'll let you know how it works out.

 

[David20]

@Jeffrey Mahoney, your solution worked exactly as I needed. Thank you for the help.

 

[Jeffrey Mahoney]

You're welcome. Thank you letting me know