Delete conditional format for "blank" Cells

cbutters

New Member
Joined
Aug 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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
'*****************************************************'
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,481
Office Version
  1. 365
Platform
  1. Windows
What is your exact Conditional Formatting formula?
The best bet may be to address it there.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,259
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

New Member
Joined
Aug 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Joe: the formulas used are below (the first sets the references of the sheets, the second does the formatting):
VBA Code:
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

VBA Code:
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

Well-known Member
Joined
Mar 29, 2006
Messages
2,259

ADVERTISEMENT

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

Anyway, the following instruction in the following position should remove "also" formatting from empty cells:
Code:
'previous instructions
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.SpecialCells(xlCellTypeBlanks).Clear
End Sub

Bye
 

cbutters

New Member
Joined
Aug 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How many cells are selected when you run the code?
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,259
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

New Member
Joined
Aug 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Update: Solved the deleting of the blank cell issue with another Sub (Sub isEmpty()) located below. Thanks for the support!
VBA Code:
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
 
Solution

cbutters

New Member
Joined
Aug 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,144,162
Messages
5,722,845
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top