Delete conditional format for "blank" Cells

[cbutters]

hello all I have a program which compairs 2 files (exported from Adobe)(on two sheets) and then conditionally highlights the differences. The problem is it highlights the "blank"/"empty" cells even if they are the same. I assume that they are not "blank/empty" because they have a border? Or maybe the export is adding something there? I have tried to right another code to find those cells and delete the formatting, but I am stuck as I am unaware how to find those specific cells? I have verified that they are not empty by using isblank AND isempty, which both return false. I have placed a snip of the code I have tried below:

Set rng = sht.Range(StartCell, sht.Cells(LastRow, LastColumn))
  
'***needs to delete out cells with no text, doesn't work***
  Worksheets("Table 1").Activate
Dim C As Range
'start of the for loop to check for filled fields
For Each C In rng
If IsEmpty(C) Then
GoTo nextc
Else
C.FormatConditions.Delete
End If
nextc:
Next C
'*****************************************************'

 

[Joe4]

What is your exact Conditional Formatting formula?
The best bet may be to address it there.

 

[Anthony47]

Formatting should not change the value of a cell, so I am afraid the cells are really different. For example what you get using the formula =LEN(OneOfTheApparentlyEMPTYCells) ?

This in addition to Joe's point (see message above)

Bye

 

[cbutters]

Joe: the formulas used are below (the first sets the references of the sheets, the second does the formatting):

Sub Select_Range_sheet_2()  'Sets References*****

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
'
'
'table 1 reference

Worksheets("Table 1").Activate
Set sht = Sheets("Table 1")
Set StartCell = Range("A1")

'Refresh UsedRange
  Sheets("Table 1").UsedRange

'Find Last Row and Column
  LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
  LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column

'Select Range
  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
  
'set as reference
    Application.Goto Reference:="Sheet1Data"
    
'
'table 2 reference
    
Worksheets("Table 2").Activate
Set sht = Sheets("Table 2")
Set StartCell = Range("A1")

'Refresh UsedRange
  Sheets("Table 2").UsedRange

'Find Last Row and Column
  LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
  LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column

'Select Range
  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
  
'set as reference
    Application.Goto Reference:="Sheet2Data"
    
    
End Sub

Sub Macro2()
'
' Macro2 Macro
' conditional formatt to highlight duplicate
'

     Application.Goto Reference:="Sheet1Data"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF(Sheet2Data,A1)=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    
End Sub

Anthony: When I do a LEN function I get "0" and if i do an =ASC(BlankButNotBlankCell) it returns an empty cell. If I do a IsBlank or Isempty they both return false. Once I remove the borders prior to formatting they return true, but once the formatting happens they go back to false (because of the format?).

 

[Anthony47]

I don't understand the way you "set reference to Table1" and Table2

Anyway, the following instruction in the following position should remove "also" formatting from empty cells:

'previous instructions
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.SpecialCells(xlCellTypeBlanks).Clear
End Sub

Bye

 

[cbutters]

When I updated the code it now deletes the contents of the whole page? At this point the two sheets are identical as I am copying them from each other to test the program.

 Application.Goto Reference:="Sheet1Data"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF(Sheet2Data,A1)=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.SpecialCells(xlCellTypeBlanks).Clear

 

[Fluff]

How many cells are selected when you run the code?

 

[Anthony47]

When I updated the code it now deletes the contents of the whole page? At this point the two sheets are identical as I am copying them from each other to test the program.

The theory with Selection.SpecialCells(xlCellTypeBlanks).Clear is that it identify the empty cells and clear them; given that they are empty, "Clear" should not alter their content, but cell formats are removed, including Conditional formats. An this is your basic request.

What did you get, instead?

Bye

 

[cbutters]

Update: Solved the deleting of the blank cell issue with another Sub (Sub isEmpty()) located below. Thanks for the support!

Sub isEmpty()

Dim r As Range, C As Range
'start of the for loop to check for filled fields
Set r = Sheets("Table 1").Range("A1:N5000")
For Each C In r
If WorksheetFunction.isBlank(C) Then
C.FormatConditions.Delete
Else: GoTo nextc:
End If
nextc:
Next C

 

[cbutters]

The theory with Selection.SpecialCells(xlCellTypeBlanks).Clear is that it identify the empty cells and clear them; given that they are empty, "Clear" should not alter their content, but cell formats are removed, including Conditional formats. An this is your basic request.

What did you get, instead?

Bye

When I ran the .clear function it cleared the entire page. text, numbers, font, format, everything. it was weird!