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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
What is your exact Conditional Formatting formula?
The best bet may be to address it there.
 
Upvote 0
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
 
Upvote 0
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?).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
How many cells are selected when you run the code?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top