Find cells in excel which have a format other than General like Custom

Question

In my excel workbook most of the cells have General as number format. However few cells have custom formatting set to them. With excel 2003 format (.xls), sometimes when huge amount of formatted data is saved then excel is ripping apart the formatting applied which is resulting in data loss with number formatted cells. So, I am looking for an option to identify such cells in the workbook which have a number format other than General, so that I can remove the custom formatting applied to them.

I am new to excel formulas / macro programming, however any suggestions in that direction would be helpful as well. The inbuilt find function was not helpful at all.

Answer 1

Please, try the next function able to create a Union range of the cells having numeric value and not being formatted as General. It should be fast enough, using an array and the above mentioned Union range, to be formatted at once, at the end:

Function findNoGeneralFormat(w As Worksheet) As Range
  Dim URng As Range, arr, Ur As Range, i As Long, j As Long
  
  Set URng = w.UsedRange
  URng.Interior.Color = xlNone 'clear the previously colored cells
                               'comment this line if this is not convenient (having already highlighted cells needed to remain colored)
  arr = URng.Value2
  For i = 1 To UBound(arr)
    For j = 1 To UBound(arr, 2)
        If IsNumeric(arr(i, j)) Then
            If URng.cells(i, j).NumberFormat <> "General" Then
                addToRange Ur, URng.cells(i, j)
            End If
        End If
    Next j
  Next i
  If Not Ur Is Nothing Then Set findNoGeneralFormat = Ur
End Function

The necessary sub to create the Union range:

Sub addToRange(rngU As Range, rng As Range)
    If rngU Is Nothing Then
        Set rngU = rng
    Else
        Set rngU = Union(rngU, rng)
    End If
End Sub

It can be used in the next way:

Sub testfindNoGeneralFormat()
   Dim ws As Worksheet, noGenFormat As Range
   Set ws = ActiveSheet
   
   Set noGenFormat = findNoGeneralFormat(ws)
   If Not noGenFormat Is Nothing Then noGenFormat.Interior.Color = vbRed
End Sub

The above code will also color Date formatted cells, if the case. It can be adapted to also skip them, if necessary.

Please, send some feedback after testing it.

Answer 2

Place this code in a module in VBA and start.

Tools -> Macro -> Visual Basic

Sub coloring()

For Each a In ActiveSheet.UsedRange
If a.NumberFormat <> "General" Then
 a.Interior.Color = RGB(200, 232, 124)
End If
Next a

End Sub

It will color all cells which is not General formatted.