Clear cell contents if Conditional Formatting is applied to it

[zero269]

Hello,

I'm trying to use this VBA code that claims to be able to clear a cells contents if conditional formatting has been applied to a cell.

FormatCondition = True?

Hi, I'm trying to use a macro to delete a cell if it IS conditional formatted. It seems right now that the colorvalue of the font is NOT stored in Font.ColorIndex, and so my IF statement based on that is not working either. Here's the statement I am trying to use: Public Sub...

www.mrexcel.com

Code from that thread:

Public Sub DeleteHighlighted()
Set rng = Selection
For Each c In rng
        If c.FomratCondition = True Then
           c.Clear
        Else
        End If
    Next c
End Sub

When I try to use the code as is, it throws a compiling error:

This code was written in 2004 so I can only assume that the Syntax has changed since then.

I'm basically trying to clear the contents from the active cell if conditional formatting has been applied to it.

I often build a list of Quiz numbers into a column. If it's a duplicate within the column, the cell will highlight Red. If it's a duplicate in the Master List located on another Sheet, it will highlight Orange.
I often just delete the cell contents once I see it's a duplicate, but I'd like to automate the process so that once I paste the value, it will automatically be cleared once the CF has been applied.
I'll know it's a duplicate when I see that the cell remains clear after pasting my value and hitting Enter.

 

[Peter_SSs]

I'm trying to use this VBA code that claims to be able to clear a cells contents if conditional formatting has been applied to a cell.

The OP in that thread did not make that claim. They said they were trying to use that code. The whole point of them posting that thread was that the code did not do what they wanted.

When I try to use the code as is, it throws a compiling error:

This code was written in 2004 so I can only assume that the Syntax has changed since then.

Best to give the full error when reporting vba error messages.
My guess is that you got a "Variable not defined" error because you have "Option Explicit" set in your module whereas that previous poster most likely did not, although I doubt they actually got to even test that posted code as it does have at least one syntax error in a later line ("Fomrat" rather than "Format")
The syntax has not changed in relation to setting a range, which appears to be the error line in your code.

If your conditional formatting is setting the cell interior to be red and there would be no manual red colour applied to any cells then you could try something like this

Sub Test()
  Dim c As Range
  
  For Each c In Range("A1:A6")
    If c.DisplayFormat.Interior.Color = vbRed Then c.ClearContents
  Next c
End Sub

 

[zero269]

Best to give the full error when reporting vba error messages.
My guess is that you got a "Variable not defined" error because you have "Option Explicit" set in your module

Hi Peter,
Thanks for looking at this for me. I corrected the typo in the code but it resulted in a "Compile error: Variable not defined".

If your conditional formatting is setting the cell interior to be red and there would be no manual red colour applied to any cells then you could try something like this

I tried using your code after updating the range. Although no errors occurred, I didn't see any cells cleared.
One thing to note is that I'm not sure about the official name for the red color that's being used by the default Conditional Formatting rule using:
Highlight Cells Rules > Duplicate Values > Light Red Fill With Dark Red Text

I know the HEX values if that's an option with VBA.

Here's a Mini Sheet of what I'm working with:

Reading Tracker.xlsm
	A
3	Quiz
4	995670
5	993564
6	123098
7	2648
8	2648
9	2860
URLs

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:A9	Cell Value	duplicates	text	NO
A4:A9	Expression	=COUNTIFS(Books!$A$4:$A$3527,$A4)	text	NO

Just to clarify what I'm trying to do:
As I paste Quiz numbers into the Table (Column A), it will highlight red (#FFC7CE) for duplicates within the column and Orange (#FFC000) for duplicates found in my Master List on another sheet.
For reference, the Table name where I'm pasting the Quiz numbers is called "tbl_URLs".
Currently, after pasting the value in the cell, I'll hit Enter and move to the next row; rinse and repeat.
If a cell gets highlighted when I hit Enter, I manually delete that cells contents only and then continue with building my list.

 

[zero269]

If your conditional formatting is setting the cell interior to be red

To test your code as written, I changed the color from that default light red to Red and it cleared the first one that was Red. That's great news!

How could I modify the code to check from the bottom up instead of top down?
It also looks like I just need to create a loop to look for the Orange as well.

I tried changing it from vbRed to vbOrange but it didn't like that.

Interestingly enough, Excel labels that color Orange under Standard Colors, but under Recent Colors it's labeled Gold. I tried both and the "compile error" was the same.
I'm going to look into using the HEX value for the color with VBA and see what I can find.

 

[Peter_SSs]

I corrected the typo in the code but it resulted in a "Compile error: Variable not defined".

Yes, as I mentioned, you are using Option Explicit

That is a very good idea and you should keep that setting. However, it does mean that every variable that you use must be declared in a 'Dim' statement. Example

Public Sub DeleteHighlighted()
  Dim Rng As Range, c As Range
 
  Set Rng = Selection
  For Each c In Rng

However, there are other errors in that code and in any case it will not do what you want so you might as well forget working with it.

If a cell gets highlighted when I hit Enter, I manually delete that cells contents only and then continue with building my list.

If you want to automate that, try this with a copy of your worksheet. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering new values in column A

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim lRGB As Long
 
  Set Changed = Intersect(Target, Range("A" & Rows.Count).End(xlUp))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      lRGB = c.DisplayFormat.Interior.Color
      If lRGB = RGB(255, 192, 0) Or lRGB = RGB(255, 199, 206) Then c.ClearContents
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[zero269]

If you want to automate that, try this with a copy of your worksheet.

Hi Peter,
Thanks for code. I followed your instructions as I understand them, but for some reason it's not running... or I'm doing something wrong.
I created a COPY of my URLs Worksheet and pasted your code to the copied Worksheet as instructed.
I'm not seeing any errors, it's just that it will only open the Macro window when I hit the Run Sub (F5) button. I looked in the list thinking I was supposed to select yours, but I didn't see it listed.
This is what I'm seeing:

 

[Peter_SSs]

You don't run this code, it happens automatically when you enter new values in Column A.

I am curious though. You said this

If a cell gets highlighted when I hit Enter, I manually delete that cells contents only and then continue with building my list.

As I understand it, that would mean if a cell gets highlighted you delete the cell contents. I presume that means that the colour goes away?
That is, every time a cell gets coloured, it gets un-coloured straight away because of the deletion.
If that is so, what was the point of applying the conditional formatting in the first place?

 

[zero269]

Hi Peter,

You don't run this code, it happens automatically when you enter new values in Column A.

I ran the test again but it's not removing the cell contents for a cell when highlighted with Conditional Formatting.
Here's an example of some values I entered manually. I then entered two values that would be flagged as duplicates in Rows 7 & 8 which are not being cleared by VBA.

As I understand it, that would mean if a cell gets highlighted you delete the cell contents. I presume that means that the colour goes away?
That is, every time a cell gets coloured, it gets un-coloured straight away because of the deletion.
If that is so, what was the point of applying the conditional formatting in the first place?

Correct... they are conditionally formatted to flag for duplicates. I use that default option for "duplicates" with CF for the current column and Orange for any duplicate values that exist in my master list of books on another Sheet.

Should I change anything in your code to indicate that my Table doesn't start with A1? My image shows it begins at A3. I use the first two Rows for my Macro buttons that perform other tasks.

 

[zero269]

UPDATE

Hi Peter,

It appears I was mistaken about your code not working on my test sheet.

I'm still playing around with it to figure out how it responds to different pasting options, using the built-in Form to add values, and when I hit Enter or Tab to continue building my list of Quiz numbers from the web.

I'll report back shortly...

 

[zero269]

Hi Peter,

I performed the following steps to see how your code responded to the various methods that can be used to add new values to Column A:

Note: All values are numerical values being copied from a web page.

I tested your codes behavior using the following methods:

1. Typing values manually
2. Clicking inside the cell to paste values.
3. Using Paste Special > Text

I noticed that as long as I hit the Enter key (as well as Ctrl+Enter), the duplicate values that are being conditionally formatted will be cleared immediately. However, if I use the Tab key (which takes me to the next [new] row), the duplicate value will not be cleared. I assume that's because your code is testing against the active cell, or perhaps the last Row only?

I looked into using Excel's built-in Form option as well, but using this method completely bypasses your code as all duplicates remained. This is something I may look into sometime in the future, but for now, your code is working very well. I just need to find a way to automate the pasting to conform with how this code responds to the different methods.

If you get a chance, could you please look at my post on this topic here?

VBA: Paste from Clipboard to Active Cell (then TAB key)

Hello, I'm having trouble pasting from the Windows Clipboard into the Active Cell in Excel. I've tried a few variations online but none of them work. The one that looks like the simplest is: Sub PasteValues() ActiveSheet.PasteSpecial Paste:=xlPasteValues End Sub However, this throws a...

www.mrexcel.com

I've been looking for a way to simplify the process of pasting values only into the active cell but have had little to no luck. This would certainly serve as a precursor to this automated duplicate removals code.

Thanks, Peter for all your help with this. I'm going to test this out now on my original Worksheet and purge the copied version that I was using as you suggested.